|
Execute the following
T-SQL scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate sequence generation, stored procedure and user-defined function creation. The sproc and the functions use the CROSS JOIN operator to create a large number of combinations (Cartesian product). The sequence generation scripts can be used imbedded into other T-SQL code.
------------
-- SQL sequence generator script
------------
-- SQL recursive cte - Common Table Expression
-- SQL maxrecursion option - SQL integer sequence
WITH cteSequence ( SeqNo) as
(
SELECT 1
UNION ALL
SELECT SeqNo + 1
FROM cteSequence
WHERE SeqNo < 1000000
)
SELECT TOP 100 * FROM cteSequence
OPTION ( MAXRECURSION 0)
GO
/* Partial results
SeqNo
1
2
3
4
5
6
7
*/
------------
-- SQL date sequence
WITH cteSequence ( SeqNo) as
(
SELECT 0
UNION ALL
SELECT SeqNo + 1
FROM cteSequence
WHERE SeqNo < 100
)
SELECT [DATE]=DATEADD(day, SeqNo, '2012-01-01')
FROM cteSequence
OPTION ( MAXRECURSION 0)
GO
/* Partial results
DATE
2012-01-01 00:00:00.000
2012-01-02 00:00:00.000
2012-01-03 00:00:00.000
2012-01-04 00:00:00.000
*/
------------
------------
-- Sequence table for an application like Purchasing
------------
-- SQL Server sequence number (1, 2, 3, 4, ....) stored in a table for sharing
USE AdventureWorks2008;
GO
CREATE TABLE PurchaseOrderSequence (
SeqID INT IDENTITY ( 1 , 1 ) PRIMARY KEY )
GO
-- SQL insert default values
INSERT PurchaseOrderSequence DEFAULT VALUES
SELECT * FROM PurchaseOrderSequence
-- 1
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
INSERT PurchaseOrderSequence DEFAULT VALUES
SELECT MAX(SeqID) FROM PurchaseOrderSequence
-- 11
-- Cleanup
DROP TABLE PurchaseOrderSequence
GO
------------
-- SQL random sequence generator - range 1 : 9999
-- SQL cte - SQL newid() function
WITH cteSequence(SeqNo)
AS (SELECT 1
UNION ALL
SELECT SeqNo + 1
FROM cteSequence
WHERE SeqNo < 10000)
SELECT TOP 100 *
FROM cteSequence
ORDER BY NEWID()
OPTION (MAXRECURSION 0)
GO
/* Partial results
SeqNo
116
8783
7169
292
6988
*/
------------
------------
-- SQL sequence generator stored procedure
-----------
use AdventureWorks;
go
create proc procSequence @Limit int
as
begin
select TOP (@Limit) ID = identity(int,1,1)
into #Seq
from sys.objects a
cross join sys.objects b
cross join sys.objects c
select * from #Seq
end
go
-- Number sequence of 100000
exec procSequence 100000
go
-- Checking generation upper limit
select ObjCount=COUNT(*),
UpperLimit=POWER(COUNT(*),3)
from sys.objects
go
/*
ObjCount UpperLimit
575 190,109,375
*/
-- Table-valued function sequence generator
create function fnSequence (@Limit int)
returns @Series table (SeqNo int)
as
begin
declare @Sequence table (SeqNo int identity(1,1), One int)
insert @Sequence (One)
select TOP (@Limit) 1
from sys.objects a
cross join sys.objects b
cross join sys.objects C
insert @Series select SeqNo from @Sequence
return
end
go
-- Number sequence of 1000
select * from fnSequence(1000) order by SeqNo
go
-- Date sequence of 100
select [Date]=DATEADD(day, SeqNo, getdate())
from fnSequence(100)
order by [Date]
go
-- Series up to 1000 with table-valued function
create function fnSeries (@Limit int)
returns @Series table (SeqNo int)
as
begin
declare @Digit table (Digit tinyint)
insert @digit values (0); insert @digit values (1);
insert @digit values (2); insert @digit values (3);
insert @digit values (4); insert @digit values (5);
insert @digit values (6); insert @digit values (7);
insert @digit values (8); insert @digit values (9);
insert @Series
select TOP (@Limit)
[Number]=hundreds.Digit * 100 + tens.Digit * 10 + ones.Digit + 1
from @Digit ones
cross join @Digit tens
cross join @Digit hundreds
order by [Number]
return
end
go
-- Squares of the first 99 numbers
select [Number]=SeqNo, [Square]=SeqNo*SeqNo
from fnSeries(99) order by SeqNo
go
------------
-- SQL sequence in a table column
------------
USE tempdb;
GO
CREATE TABLE ShortProductInfo (
ProductID int,
ProductName varchar(50),
ListPrice money,
Color varchar(16),
Sequence int identity(500, 2)
)
GO
INSERT ShortProductInfo ( ProductID, ProductName, ListPrice, Color)
SELECT ProductID, Name, ListPrice, Color
FROM AdventureWorks.Production.Product
WHERE Color is not null
GO
SELECT * FROM ShortProductInfo ORDER BY Sequence
GO
/* Partial results
ProductID ProductName ListPrice Color Sequence
317 LL Crankarm 0.00 Black 500
318 ML Crankarm 0.00 Black 502
319 HL Crankarm 0.00 Black 504
320 Chainring Bolts 0.00 Silver 506
321 Chainring Nut 0.00 Silver 508
322 Chainring 0.00 Black 510
*/
------------
------------
-- SQL sequence with permanent table
------------
-- SQL create table to "remember" the sequence position
USE AdventureWorks;
GO
CREATE TABLE POSequence (
PONumber int identity(1,1) primary key
)
GO
-- SQL create procedure to get the next sequenctial value
CREATE PROCEDURE sprocGetNewPONumber
AS
BEGIN
SET NOCOUNT ON
DECLARE @NextPO int
INSERT POSequence DEFAULT VALUES
SELECT @NextPO = SCOPE_IDENTITY()
DELETE POSequence
RETURN @NextPO
END
GO
-- SQL next sequential number
DECLARE @PO int
EXEC @PO=sprocGetNewPONumber
SELECT @PO
GO 10
/* Results
1
2
3
4
5
6
7
8
9
10
*/
-- Cleanup
DROP TABLE POSequence
GO
------------
-- SQL sequence generation with table variable
-- SQL insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int = 0
WHILE ( @i < 500)
BEGIN
INSERT @Sequence DEFAULT VALUES
SET @i += 1
END
SELECT * FROM @Sequence
GO
/* Partial result
Sequence
1
2
3
4
5
6
7
8
*/
-- SQL GUID generate sequence
USE tempdb;
GO
CREATE TABLE testGUID (RowGUID uniqueidentifier DEFAULT NEWSEQUENTIALID())
GO
-- SQL generate 100 sequential GUID values
-- SQL insert default values
INSERT INTO testGUID DEFAULT VALUES
GO 100
SELECT *
FROM testGUID
GO
/* Partial results
RowGUID
A810B32A-66D8-DD11-8AF3-0013723AB734
A910B32A-66D8-DD11-8AF3-0013723AB734
AA10B32A-66D8-DD11-8AF3-0013723AB734
AB10B32A-66D8-DD11-8AF3-0013723AB734
AC10B32A-66D8-DD11-8AF3-0013723AB734
AD10B32A-66D8-DD11-8AF3-0013723AB734
*/
DROP TABLE testGUID
GO
------------
-- T-SQL make ID sequential - remap random ID to identity
------------
-- SQL remap ID to sequential ID
-- Change non-sequential ID to sequential number
USE tempdb;
SELECT *
INTO SOD
FROM AdventureWorks.Sales.SalesOrderDetail
GO
SELECT TOP 5 *
FROM SOD
ORDER BY SalesOrderID,
SalesOrderDetailID
/* Partial results
SalesOrderID SalesOrderDetailID
43659 1
43659 2
43659 3
43659 4
43659 5
*/
-- SQL generate identity sequence with mapping to ID with gaps
-- SQL identity gaps - set identity_insert sql - Gaps in SQL Server identity columns
;WITH cteDistinctSO AS
(
SELECT DISTINCT SalesOrderID FROM SOD
)
SELECT [NewID]=Identity(int,1,1), SalesOrderID
INTO SequentialIDMapping
FROM cteDistinctSO
ORDER BY SalesOrderID ASC
GO
SELECT TOP 5 * FROM SequentialIDMapping
ORDER BY SalesOrderID
GO
/*
NewID SalesOrderID
1 43659
2 43660
3 43661
4 43662
5 43663
*/
UPDATE s
SET s.SalesOrderID = i.[NewID]
FROM SOD s
INNER JOIN SequentialIDMapping i
ON s.SalesOrderID = i.SalesOrderID
GO
SELECT TOP 100 *
FROM SOD
ORDER BY SalesOrderID,
SalesOrderDetailID
GO
/* Partial results
SalesOrderID SalesOrderDetailID
SalesOrderID SalesOrderDetailID
3 17
3 18
3 19
3 20
3 21
3 22
3 23
3 24
3 25
3 26
3 27
3 28
3 29
4 30
4 31
4 32
4 33
4 34
4 35
4 36
4 37
4 38
4 39
4 40
4 41
4 42
4 43
4 44
4 45
4 46
4 47
4 48
4 49
4 50
4 51
5 52
6 53
6 54
6 55
6 56
6 57
6 58
6 59
6 60
7 61
7 62
7 63
7 64
7 65
7 66
7 67
7 68
7 69
7 70
*/
-- Cleanup
DROP TABLE SOD
DROP TABLE SequentialIDMapping
GO
------------
|
