How to get next ID for the table - SQL Server

How to get next ID for the table - SQL Server

Usually we need to show the next ID on the new entry form, like Sale Order , Purchase Order..etc.

There are different methods for achieve this as far as I know: 

1. Create a function or stored procedure to to query the max ID for the table that we want to know the last ID generated and add 1 to the ID.

For example :

Select IsNull(Max(PurchaseOrderId),0) + 1 from PurchaseOrder

But this method will only work only when we have atleast one record in the table, other wise the Max function will return NULL, and it will be converted to zero as we have used ISNULL function and the ID which we will get will be 1, and that could only be correct if you have not inserted any record in the table, otherwise if you have entered a record in the table and deleted that record, its ID will be starting from the last ID generated.

2. Another method, that is usually used is that we check if the query / resultant of the above query is NULL, we reseed the table identity column to 1.

For example:

declare @nextId as int

set @nextId  = (Select Max(PurchaseOrderId) from PurchaseOrder)

IF @nextId IS NULL

 BEGIN

     DBCC CHECKIDENT ('PurchaseOrder', RESEED, 1)

  

 END

Select IsNull(@nextId,0) +1 as NextIdentity

which seems to be quite better way to achieve the next ID for a table.

But the best way I have found yet is :

3. There is a built-infunction in SQL SERVER 2005 AND LATER, and that is :

IDENT_CURRENT( 'table_name' )

Ident_Current() function gives you the last ID which was generated for the particular table.

For example :

Select IsNull(IDENT_CURRENT( 'PurchaseOrder' ) ,0) + 1 as NextIdentity

Now its upto you that how you use this function, I have created a general stored procedure, which accepts an argument for the table name, and passed that arguemnt /variable to the Ident_Current() function, and now I don't have to create seperate funtion / stored procedure for each table.

CREATE PROCEDURE [dbo].[GetCurrentIdentityForTable]

@TableName varchar(100)

AS

BEGIN

SELECT IsNull(IDENT_CURRENT( @TableName ),0) + 1 as CurrentIdentity

END

That was all!! If you can suggest more better method to achieve this or any other alternative, please feel free to share that :-)..