|
Execute the following
Microsoft SQL Server T-SQL database scripts in Manangement Studio Query Editor to demonstrate how generate small and large sequences.
-- CROSS JOIN number sequence generation
SELECT INTNO = ROW_NUMBER() OVER (ORDER BY a.name)
FROM sys.objects a CROSS JOIN sys.objects b CROSS JOIN sys.objects c
ORDER BY INTNO
------------
-- Generate the first 2048 integers
SELECT SEQUENCE=NUMBER FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P' ORDER BY SEQUENCE
------------
-- SQL Server recursive sequence generator script - SQL integer sequence
-- SQL recursive cte - Common Table Expression - SQL maxrecursion option
WITH CTE ( SeqNo) as
(
SELECT 1
UNION ALL
SELECT SeqNo + 1
FROM CTE
WHERE SeqNo < 1000000
)
SELECT TOP 1000 * FROM CTE ORDER BY SeqNo
OPTION ( MAXRECURSION 0)
GO
-- Simple SQL sequence generation syntax
-- SQL Server sequence number generator table-valued function
CREATE FUNCTION dbo.fnSequenceGenerator (@Limit INT)
RETURNS @Sequence TABLE(SequentialNumber INT)
AS
BEGIN
DECLARE @RunningValue INT = 1
WHILE @RunningValue <= @Limit
BEGIN
INSERT @Sequence
VALUES(@RunningValue)
SET @RunningValue += 1
END
RETURN
END
GO
SELECT * FROM dbo.fnSequenceGenerator(11)
GO
/*
SequentialNumber
1
2
3
4
5
6
7
8
9
10
11
*/
------------
CTEs (Common Table Expression) are used with CROSS JOIN for the generation of a numeric series. You can easily customize the script for shorter sequence generation.
use AdventureWorks2008
go
-- SQL cte - Common Table Expression
-- SQL sequence
-- Create a cte to give the sequence of 0-9
with cteDigits as
( select 0 as Digit
union select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9),
-- Create a second cte to generate a million number sequence
cteMillion as (
select SeqNo=hundredT.Digit * 100000+tenT.Digit * 10000 +
thousands.Digit *1000 + hundreds.Digit * 100 +
tens.Digit * 10 + ones.Digit + 1
from cteDigits as ones
-- SQL cross join
cross join cteDigits as tens
cross join cteDigits as hundreds
cross join cteDigits as thousands
cross join cteDigits as tenT
cross join cteDigits as hundredT )
-- Main query
-- SQL select from cte
select top 1000 SeqNo
from cteMillion
order by SeqNo
go
/* Partial results
SeqNo
1
2
3
4
5
6
7
8
*/ |
