DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to generate a million number sequence with CROSS JOIN?

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

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE