Ada Lovelace: Tạo sequence .

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

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

/* SeqNo

.....*/

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

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

/* 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 ... */

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

-- Generate sample sequence for datetime hierarchy: QtrHour, Hour, Day, Month, Quarter, Year

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

DECLARE @StartDate datetime = '20000101';

WITH seqCTE ( Seq15Min, SeqNo) as

(

SELECT @StartDate, 0

UNION ALL

SELECT DATEADD(n, 15,Seq15Min), SeqNo+1

FROM seqCTE

WHERE Seq15Min <= CURRENT_TIMESTAMP

)

SELECT QtrHourDate = Seq15Min,

HourDate = DATEADD(hour, DATEDIFF(hour, 0, Seq15Min), 0),

DayDate = DATEADD(day, DATEDIFF(day, 0, Seq15Min), 0),

MonthDate = DATEADD(month, DATEDIFF(month, 0, Seq15Min), 0),

QuarterDate = DATEADD(quarter, DATEDIFF(quarter, 0, Seq15Min), 0),

YearDate = DATEADD(year, DATEDIFF(year, 0, Seq15Min), 0)

FROM seqCTE ORDER BY QtrHourDate

OPTION ( MAXRECURSION 0);

QtrHourDate HourDate DayDate MonthDate QuarterDate YearDate

2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 00:15:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 00:30:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 00:45:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 01:00:00.000 2000-01-01 01:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 01:15:00.000 2000-01-01 01:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 01:30:00.000 2000-01-01 01:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

2000-01-01 01:45:00.000 2000-01-01 01:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 2000-01-01 00:00:00.000

....

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

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;

CREATE FUNCTION fnLeadingZeroes (@Zeroes int, @IntNumber int)

RETURNS varchar(32)

BEGIN

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

END

CREATE TABLE PurchaseOrderSequence (

SeqID INT IDENTITY ( 1 , 1 ) PRIMARY KEY,

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

)

-- SQL insert default values

INSERT PurchaseOrderSequence DEFAULT VALUES

SELECT * FROM PurchaseOrderSequence

/* 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)

/* Partial results

SeqNo

116

8783

7169

292

6988

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

-- SQL sequence generator stored procedure using CROSS JOINs

-----------

USE AdventureWorks;

CREATE PROC sprocSequence @Limit INT

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

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

-- Checking sequence generation upper limit

SELECT ObjCount = COUNT(* ),

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

FROM MASTER.dbo.spt_values

ObjCount UpperLimit

2506 15737770216

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

-- Table-valued function sequence generator

CREATE FUNCTION fnSequence (@Limit INT)

RETURNS @Series TABLE(SeqNo INT)

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

-- Number sequence of 1000

SELECT *

FROM fnSequence(1000)

ORDER BY SeqNo

-- Date sequence of 100

SELECT [Date] = DATEADD(DAY,SeqNo,getdate())

FROM fnSequence(100)

ORDER BY [Date]

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

-- Series up to 1000 with table-valued function

create function fnSeries (@Limit int)

returns @Series table (SeqNo int)

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

-- Squares of the first 99 numbers

select [Number]=SeqNo, [Square]=SeqNo*SeqNo

from fnSeries(99) order by SeqNo

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

-- SQL sequence in a table column

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

USE tempdb;

CREATE TABLE ShortProductInfo (

ProductID int,

ProductName varchar(50),

ListPrice money,

Color varchar(16),

Sequence int identity(500, 2)

)

INSERT ShortProductInfo ( ProductID, ProductName, ListPrice, Color)

SELECT ProductID, Name, ListPrice, Color

FROM AdventureWorks.Production.Product

WHERE Color is not null

SELECT * FROM ShortProductInfo ORDER BY Sequence

/* 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;

CREATE TABLE POSequence (

PONumber int identity(1,1) primary key

)

-- SQL create procedure to get the next sequenctial value

CREATE PROCEDURE sprocGetNewPONumber

BEGIN

SET NOCOUNT ON

DECLARE @NextPO int

INSERT POSequence DEFAULT VALUES

SELECT @NextPO = SCOPE_IDENTITY()

DELETE POSequence

RETURN @NextPO

END

-- SQL next sequential number

DECLARE @PO int

EXEC @PO=sprocGetNewPONumber

SELECT @PO

GO 10

/* Results

-- Cleanup

DROP TABLE POSequence

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

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

/* Partial result

Sequence

-- SQL GUID generate sequence

USE tempdb;

CREATE TABLE testGUID (RowGUID uniqueidentifier DEFAULT NEWSEQUENTIALID())

-- SQL generate 100 sequential GUID values

-- SQL insert default values

INSERT INTO testGUID DEFAULT VALUES

GO 100

SELECT *

FROM testGUID

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

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

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

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

SELECT TOP 5 * FROM SequentialIDMapping

ORDER BY SalesOrderID

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

SELECT TOP 100 *

FROM SOD

ORDER BY SalesOrderID,

SalesOrderDetailID

/* Partial results

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

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

Source : sqlursor

Ada Lovelace

Tuesday, January 27, 2015

Tạo sequence .

No comments:

Post a Comment