MyTechFinds.com

An identity column in SQL Server creates an auto-incrementing numeric sequence of values. When an insert is made into a table with an identity column, a value does not need to be put in the identity column. The value of the identity column is automatically filled in.

Identity columns are used when it is required to automatically number each row as it comes into the table. The identity column’s seed (starting value) and increment can be set at the time of creation of the identity column. So every time a row is added to the table it is assigned a number obtained by adding the increment to the old value (this value will be the seed for the first row)

This works very well for situations where every row requires a new number. However there are situations where business logic demands that there be a numeric sequence, however it is not necessary that every row have a new number. The number should be incremented as per the business logic and inserted into the row. 

In short if we need control on when the value of the field should be incremented, then we need to use an alternative method. This article describes a way to achieve this through source code / programmatically.

Solution

1. Create a table with one column.
2. The content of this column at all times will be the next value.
3. Create a stored procedure that will return the value of the column.
4. This stored procedure will store the value of the column in a variable, increment the id by 1 and update the table with the incremented value.
5. Finally the stored procedure will return the value stored in the variable. 

The script for creating the table is as follows 

  
CREATE TABLE [dbo].[tbl_Test_Bucket] (
      [IDNum] [int] NOT NULL 
) ON [PRIMARY]
 

Insert 1 row in this table with the value 1

 
insert into tbl_Test_Bucket values (1)
) ON [PRIMARY]

The code for the stored procedure that returns the incremented id is as follows

 
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO 
CREATE PROC [dbo].[usp_GetNextAvailableID]
AS
DECLARE @NextIDNum VARCHAR(15) 
BEGIN TRANSACTION 
SELECT @NextIDNum = IDNum FROM tbl_Test_bucket
UPDATE tbl_Test_bucket SET IDNum = IDNum + 1
SELECT @NextIDNum AS NextId 
IF @@ERROR 0
    BEGIN
      ROLLBACK TRANSACTION
    END
ELSE
    BEGIN
      COMMIT TRANSACTION
    END
GO

Calling the stored procedure from C# 

Whenever a new id is required, the stored procedure above can be invoked from C# code as follows 

 
public string GetNextInvPOID()
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
                connection.Open();
                SqlCommand command = new SqlCommand("usp_GetNextAvailableID", connection);
                command.CommandType = CommandType.StoredProcedure;
                return command.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

This way we can have control over when to increment the value of the column.

Application

A simple application of this can be while generating Invoice numbers for items sold in a retail shop.

Since multiple items can be listed on a single invoice, it is required that a new invoice number be generated not for every item sold but for every new customer.

In such a case whenever the program detects that a new invoice number has to be generated it can make a call to the stored procedure and get the new id.