Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor demonstrate the use of row_number() and identity() functions for sequential numbering.

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

 
*/
-- Cleanup
DROP TABLE #ProductsByCategory
GO

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

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