How to generate Sequence number in SQL Server

Sequence number generation is one of the common requirements in all the OLTP applications.SQL Server supports many ways to generate Sequence numbers. The below example explains how to generate multiple sequences dynamically using the SQL Server

Schema Design

This table will hold the configuration parameters for each of the Sequence Types (eg: PurchaseOrder, GRN etc).The column “SequenceName” column will have the unique code for each Sequence type and the “TableName” column is used to map the name of the “IDGenerator” table. (I.e. multiple IDGenerator tables can be created with the same structure and mapped accordingly)

ID Generation:
The below stored procedure pGetSEQID is used to generate the new sequence number .It accepts the table name as input and return the new sequence number. Since the IDGenerator table can be different for each ID type the stored procedure is written as dynamic.

CREATE PROCEDURE pGetSEQID (
@P_SEQTableName VARCHAR(30)
)
AS
BEGIN
DECLARE @V_SEQValue BIGINT
DECLARE @V_SQLString NVARCHAR(200)
DECLARE @V_ParmDefinition NVARCHAR(200)
DECLARE @V_SEQTableName VARCHAR(30)
SET @V_ParmDefinition = N'@V_SEQValue BIGINT OUTPUT'
SET @V_SQLString = N'INSERT INTO ' +
@P_SEQTableName +
N'(Status) values (''Y'') SELECT @V_SEQValue = SCOPE_IDENTITY()'
exec sp_executesql @V_SQLString,@V_ParmDefinition ,@V_SEQValue =@V_SEQValue OUTPUT
SELECT @V_SEQValue NewSequenceValue
END

EXEC pGetSEQID ‘PurchaseOrder’ will generate the Sequence Number for ID type purchase order

We can make use of Prefix and Suffix columns in the IDParamter table to generate the Sequence number with the required format
Eg: “BL0001” , BL100/0908

How to generate ROWNUM in SQL Server SELECT

It is often required to create unique values for every row in a result set query in SQL Server. But SQL Server doesn’t have any built in functions such as ROWNUM in oracle to support this feature. There are many ways in which we can generate unique numbers in the select query as shown in the below scenario

Consider the Employee table with EmployeeName and EmployeeID columns and the scenario is to select records from the table based on EmployeeID with uniqueid for each row in the result set. The select statement can be written in below ways

Create table Employee ( EmployeeID INT , EmployeeName Varchar(30))
ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName
FROM Employee ORDER BY EmployeeID
ROWNUM using IDENTITY
SELECT IDENTITY(int, 100, 1) AS RowNumber , EmployeeName
INTO #tmp
FROM Employee ORDER BY EmployeeID
SELET RowNumber , EmployeeID FROM #tmp
ROWNUM using NEWID
SELECT NEWID() ,AS RowNumber, EmployeeName FROM Employee
ORDER BY EmployeeID
Related article:
http://samsudeenb.blogspot.com/2009/06/how-to-generate-sequence-number-in-sql.html