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

GO

/*

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