Ada Lovelace: Sử dụng sequence

Showing posts with label Sử dụng sequence. Show all posts

Tuesday, January 27, 2015

Tạo các giá trị tự tăng trong SQL .

-- SQL Server add row number - sql server row column - row_number sql server
-- T SQL row numbering - QUICK SYNTAX - sql row number column - select row_number
-- MSSQL Server add new sequential ProductID to table using row_number() function
-- Oracle rowid SQL Server - sql server 2005 row id - rowid equivalent sql server
SELECT NewProductID = ROW_NUMBER() OVER (ORDER BY ProductID), *
INTO #Product
FROM AdventureWorks.Production.Product
GO
SELECT * FROM #Product
/* Partial results
NewProductID      ProductID   Name
1                 1           Adjustable Race
2                 2           Bearing Ball
3                 3           BB Ball Bearing
4                 4           Headset Ball Bearings
5                 316         Blade
6                 317         LL Crankarm
7                 318         ML Crankarm
*/
-- Cleanup
DROP TABLE #Product
------------

-- T-SQL using the IDENTITY function for row numbering

DECLARE @Product TABLE(ID INT IDENTITY(1,1), ProductID int, ProductName varchar(64),

ListPrice money, Color varchar(32))

INSERT @Product(ProductID, ProductName, ListPrice, Color)

SELECT ProductID, Name, ListPrice, Color

FROM AdventureWorks2008.Production.Product

WHERE ListPrice > 0 AND Color is not null

ORDER BY Name

SELECT TOP(3) * FROM @Product ORDER BY ID

/* ID ProductID ProductName ListPrice Color

1 712 AWC Logo Cap 8.99 Multi

2 952 Chain 20.24 Silver

3 866 Classic Vest, L 63.50 Blue */

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

-- T SQL row numbering groups with partition by - row number each group - sequence
-- SQL row number each salesperson within a country with sales descending
-- MSSQL select into temporary table - create temp table
SELECT LastName + ', ' + FirstName     AS SalesPerson,
       CountryRegionName               AS Country,
       ROW_NUMBER()
         OVER(PARTITION BY CountryRegionName
              ORDER BY SalesYTD DESC) AS 'Row Number',
       '$'+convert(varchar,SalesYTD,1) AS SalesYTD
INTO #SalesPersonRank
FROM   AdventureWorks2008.Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
       AND SalesYTD <> 0;
GO
SELECT * FROM #SalesPersonRank ORDER BY Country, [Row Number]
/* Partial results
SalesPerson       Country           Row Number SalesYTD
Valdez, Rachel    Germany           1           $2,241,204.04
Pak, Jae          United Kingdom    1           $5,015,682.38
Mitchell, Linda   United States     1           $5,200,475.23
Blythe, Michael   United States     2           $4,557,045.05
Carson, Jillian   United States     3           $3,857,163.63
Campbell, David   United States     4           $3,587,378.43
Ito, Shu          United States     5           $3,018,725.49
*/
DROP TABLE #SalesPersonRank
------------

-- SQL adding identity column to a table - sql sequential numbering
USE tempdb;
CREATE TABLE Department(
      Name varchar(32),
      GroupName varchar(256),
      ModifiedDate datetime
)
GO
ALTER TABLE Department ADD    DepartmentID smallint IDENTITY(1,1)
GO
-- Only one identity column per table
ALTER TABLE Department ADD    SecondIdentity smallint IDENTITY(1,1)
GO
/*
Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Department'. Only one identity
column per table is allowed.
*/
-- SQL identity insert enabled - sql insert identity column
SET IDENTITY_INSERT Department ON
-- SQL identity insert disabled (default)
SET IDENTITY_INSERT Department OFF
-- SQL reseeding identity column - reset identity column
DBCC CHECKIDENT ("dbo.Department", RESEED, 999)

DROP TABLE tempdb.dbo.Department
GO
------------

-- MSSQL add new partition row ID to table using row_number() function
-- Microsoft SQL Server T-SQL row number over partition by order by
SELECT      RowID = ROW_NUMBER() OVER (
                  PARTITION BY p.ProductSubcategoryID ORDER BY ProductID),
      SubCategory = ps.Name,
      ProductName = p.Name,
      ProductNumber,
      Color,
      ListPrice
INTO #ProductsByCategory
FROM AdventureWorks.Production.Product p
INNER JOIN AdventureWorks.Production.ProductSubcategory ps
      ON p.ProductSubcategoryID= ps.ProductSubcategoryID
GO
SELECT * FROM #ProductsByCategory
GO
/* Partial results

RowID	SubCategory	ProductName	ProductNumber	Color	ListPrice
1	Handlebars	LL Mountain Handlebars	HB-M243	NULL	44.54
2	Handlebars	ML Mountain Handlebars	HB-M763	NULL	61.92
3	Handlebars	HL Mountain Handlebars	HB-M918	NULL	120.27
4	Handlebars	LL Road Handlebars	HB-R504	NULL	44.54
5	Handlebars	ML Road Handlebars	HB-R720	NULL	61.92
6	Handlebars	HL Road Handlebars	HB-R956	NULL	120.27
7	Handlebars	LL Touring Handlebars	HB-T721	NULL	46.09
8	Handlebars	HL Touring Handlebars	HB-T928	NULL	91.57
1	Bottom Brackets	LL Bottom Bracket	BB-7421	NULL	53.99
2	Bottom Brackets	ML Bottom Bracket	BB-8107	NULL	101.24
3	Bottom Brackets	HL Bottom Bracket	BB-9108	NULL	121.49
1	Brakes	Rear Brakes	RB-9231	Silver	106.5
2	Brakes	Front Brakes	FB-9873	Silver	106.5
1	Chains	Chain	CH-0234	Silver	20.24
1	Cranksets	LL Crankset	CS-4759	Black	175.49
2	Cranksets	ML Crankset	CS-6583	Black	256.49
3	Cranksets	HL Crankset	CS-9183	Black	404.99

*/
-- Cleanup
DROP TABLE #ProductsByCategory
------------

-- SQL add row number and rank number to SELECT INTO table create
-- Rank (dense ranking) high price items to low price items
SELECT   ROW_NUMBER()
           OVER(ORDER BY Name ASC) AS ROWID,
         DENSE_RANK()
           OVER(ORDER BY ListPrice DESC) AS RANKID,
         ListPrice                       AS Price,
         *
INTO tempdb.dbo.RankedProduct
FROM     AdventureWorks2008.Production.Product
ORDER BY RANKID, ROWID
SELECT * FROM tempdb.dbo.RankedProduct
/* Partial results

ROWID RANKID      Price       ProductID   Name
376   1           3578.27     750         Road-150 Red, 44
377   1           3578.27     751         Road-150 Red, 48
378   1           3578.27     752         Road-150 Red, 52
379   1           3578.27     753         Road-150 Red, 56
380   1           3578.27     749         Road-150 Red, 62
332   2           3399.99     771         Mountain-100 Silver, 38
333   2           3399.99     772         Mountain-100 Silver, 42
334   2           3399.99     773         Mountain-100 Silver, 44
335   2           3399.99     774         Mountain-100 Silver, 48
*/
------------

-- SQL add sequential row number (rowid) to table using identity(int,1,1) function
USE tempdb;
SELECT [SalesOrderID]=CONVERT(int, [SalesOrderID])
      ,[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
      ,[OnlineOrderFlag]
      ,[SalesOrderNumber]
      ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[ContactID]
      ,[SalesPersonID]
      ,[TerritoryID]
      ,[BillToAddressID]
      ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
      ,[CreditCardApprovalCode]
      ,[CurrencyRateID]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
      ,[Comment]
      ,[rowguid]
      ,[ModifiedDate]
INTO     SOH
FROM     AdventureWorks.Sales.SalesOrderHeader
ORDER BY OrderDate,
         CustomerID
GO

-- SQL Server T-SQl identity function for sequence generation - sequential ID
SELECT   SequentialRowNumber = identity(INT,1,1),
         *
INTO     #SOH
FROM     SOH
ORDER BY OrderDate,
         CustomerID

GO

-- The following step is normally done by sp_rename (cannot do it for temp table)
-- Microsoft SQL Server rename table example - old table renamed for future delete
-- EXEC sp_rename   SalesOrderHeader ,   zzzSalesOrderHeader
-- SQL sp rename - new table takes its place
SELECT * INTO SalesOrderHeader FROM #SOH
GO

SELECT   *
FROM     SalesOrderHeader
ORDER BY SequentialRowNumber

GO
/* Partial results

SequentialRowNumber     SalesOrderID
1                       43676
2                       43695
3                       43674
4                       43660
5                       43672
6                       43665
7                       43688
*/
-- Cleanup
DROP TABLE SOH
DROP TABLE #SOH
DROP TABLE tempdb.dbo.SalesOrderHeader GO
------------

-- Sequence numbering subsets(partitions) with standard SQL only

-- without the use of IDENTITY or ROW_NUMBER (see LineItem)

USE Northwind;

SELECT odet.OrderID,

SeqNo AS LineItem,

odet.ProductID,

UnitPrice,

Quantity AS Qty,

Discount = CONVERT(NUMERIC(3,2),Discount),

LineTotal = CONVERT(NUMERIC(12,2),UnitPrice*Quantity*(1.0-Discount))

FROM [Order Details] odet

INNER JOIN

(SELECT count(* ) SeqNo,

a.OrderID,

a.ProductID

FROM [Order Details] A

INNER JOIN [Order Details] B

ON A.ProductID >= B.ProductID

AND A.OrderID = B.OrderID

GROUP BY A.OrderID,

A.ProductID) a

ON odet.OrderID = a.OrderID

AND odet.ProductID = a.ProductID

WHERE odet.OrderID < 10300

ORDER BY odet.OrderID,

odet.ProductID,

SeqNo

OrderID LineItem ProductID UnitPrice Qty Discount LineTotal

10248 1 11 14.00 12 0.00 168.00

10248 2 42 9.80 10 0.00 98.00

10248 3 72 34.80 5 0.00 174.00

10249 1 14 18.60 9 0.00 167.40

10249 2 51 42.40 40 0.00 1696.00

10250 1 41 7.70 10 0.00 77.00

10250 2 51 42.40 35 0.15 1261.40

10250 3 65 16.80 15 0.15 214.20

..... */
------------

Source : sqlusa

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