datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to generate a sequence?

Execute the following T-SQL scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate numeric and alphanumeric 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 - Sequence object simulation in T-SQL

------------

-- SQL recursive cte - Common Table Expression - autoincrement - numeric sequence

-- SQL maxrecursion option - SQL integer sequence - sequence number generator

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

/* SeqNo

1

2

3

4

5

6

7

.....*/

------------

-- Sequence numbering subsets with windowing function - QUICK SYNTAX

SELECT Color, SeqNo=ROW_NUMBER() OVER

                    (PARTITION BY Color ORDER BY ProductNumber),

       ProductNumber

FROM AdventureWorks2008.Production.Product

WHERE Color is not null

ORDER BY Color, SeqNo

/* Color          SeqNo ProductNumber

.....

Silver/Black      6     PD-R853

Silver/Black      7     PD-T852

White             1     SO-B909-L

White             2     SO-B909-M

White             3     SO-R809-L

White             4     SO-R809-M

Yellow            1     BK-R64Y-38

Yellow            2     BK-R64Y-40

Yellow            3     BK-R64Y-42   ..... */

----------------------

-- SQL Server build / generate number table - QUICK SYNTAX

DECLARE  @Sequence  TABLE(  Number INT );

 

WITH CTE(nbr)

     AS (SELECT 1 AS nbr

         UNION ALL

         SELECT nbr + 1

         FROM   CTE  WHERE  nbr < 100000)

        

INSERT INTO @Sequence (Number)

SELECT nbr FROM   CTE

OPTION (MAXRECURSION 0);

 

SELECT Number FROM   @Sequence;

------------

-- Integer number sequence generator - QUICK SYNTAX - easy to remember

;WITH cteNumber AS

 (SELECT Number=ROW_NUMBER() OVER(ORDER BY a.name)

  FROM sys.objects a,sys.objects b)

SELECT * FROM cteNumber

------------

------------

-- Get previous 12 quarters excluding current quarter - QUARTER SEQUENCE GENERATOR

------------

DECLARE @RollingMonth DATE ='20120201';

DECLARE @QuarterStart DATE = CONVERT(DATE, Dateadd(qq, Datediff(qq, 0,

                             @RollingMonth), 0 ));

 

WITH ctesequence ( seqno)

     AS (SELECT 0

         UNION ALL

         SELECT seqno + 1

         FROM   ctesequence

         WHERE  seqno < 12 - 1)

SELECT [Quarter]=Dateadd(qq, -seqno - 1, @QuarterStart)

FROM   ctesequence;

/* Quarter

2011-10-01

2011-07-01

2011-04-01

2011-01-01

2010-10-01

2010-07-01

2010-04-01

2010-01-01

2009-10-01

2009-07-01

2009-04-01

2009-01-01  */

------------ 

-- SQL date sequence - SQL Server date range generator - datetime 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

*/
---------

-- Generate list of months

;WITH CTE AS

(

      SELECT      1 MonthNo, CONVERT(DATE, '19000101') MonthFirst

      UNION ALL

      SELECT      MonthNo+1, DATEADD(Month, 1, MonthFirst)

      FROM  CTE

      WHERE Month(MonthFirst) < 12

)

SELECT  MonthNo AS MonthNumber,

        DATENAME(MONTH, MonthFirst) AS MonthName

FROM  CTE

ORDER BY MonthNo

/* MonthNumber    MonthName

1     January

2     February

3     March  ... */

------------

-- Sequence 0 - 2047 generator using a system table

select Sequence=number from master.dbo.spt_values

where type = 'P' order by Sequence

-- (2048 row(s) affected)

/* Sequence

      0

      1

      2

      3

      4

      5  ..... */

------------

-- SQL Server alpha sequence generation - nested CTE-s - alphabetic sequence

WITH cteINTSequence(SeqNo)

     AS (SELECT 0

         UNION ALL

         SELECT SeqNo + 1

         FROM   cteINTSequence

         WHERE  SeqNo < 1000000),

     cteALPHASequence(SeqAlpha)

     AS (SELECT CHAR(65 + SeqNo / 17576) + CHAR(65 + (SeqNo%17576) / 676) +

                CHAR(65 + (SeqNo%676) / 26) + CHAR(65 + SeqNo%26)

         FROM   cteINTSequence)

SELECT TOP 100 *

FROM   cteALPHASequence

OPTION (MAXRECURSION 0)

/*    SeqAlpha

      AAAA

      AAAB

      AAAC

      AAAD

      AAAE

      AAAF

      ...  */

------------

------------

-- 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 FUNCTION fnLeadingZeroes (@Zeroes int, @IntNumber int)

RETURNS varchar(32)

AS

BEGIN

   RETURN RIGHT(REPLICATE('0',32)+CONVERT(varchar(32),@IntNumber),@Zeroes)

END

GO

CREATE TABLE PurchaseOrderSequence (

      SeqID INT IDENTITY ( 1 , 1 ) PRIMARY KEY,

      PONumber AS dbo.fnLeadingZeroes(5,SeqID) -- UDF computed column

)

GO

 

-- SQL insert default values

INSERT PurchaseOrderSequence DEFAULT VALUES

 

SELECT * FROM PurchaseOrderSequence

GO

/*    SeqID PONumber

      1     00001

*/

 

INSERT PurchaseOrderSequence DEFAULT VALUES

GO 10

 

SELECT MAX(SeqID) FROM PurchaseOrderSequence

-- 11

 

-- Cleanup

DROP TABLE PurchaseOrderSequence

DROP FUNCTION  fnLeadingZeroes

------------


------------

 

-- 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 using CROSS JOINs

-----------

USE AdventureWorks;

GO

 

CREATE PROC sprocSequence @Limit INT

AS

  BEGIN

    SELECT TOP ( @Limit ) ID = identity(INT,1,1)

    INTO   #Seq

    FROM   MASTER.dbo.spt_values a

           CROSS JOIN MASTER.dbo.spt_values b

           CROSS JOIN MASTER.dbo.spt_values c

    SELECT *

    FROM   #Seq

  END

GO

EXEC sprocSequence 100000
------------

-- Checking sequence generation upper limit

SELECT ObjCount = COUNT(* ),

       UpperLimit = POWER(convert(BIGINT,COUNT(*)),3)

FROM   MASTER.dbo.spt_values

GO

/*

ObjCount    UpperLimit

2506        15737770216

*/
------------

 

-- 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

------------

Related articles:

How to dynamically number rows in a SELECT Transact-SQL statement

Creating a Number (Sequentially incrementing values) table in T-SQL

How would you implement sequences in Microsoft SQL Server?

 

Exam Prep 70-461
Exam 70-461