SYSK 124: IDENT_CURRENT Without an Identity Column


SQL Server’s function IDENT_CURRENT(‘tablename’) returns the last identity value set on a specified table.  While this is very useful, sometimes you need to get the next available primary key on a column that is not an identity field.  In this case,


SELECT IDENT_CURRENT (‘tablename’) will return NULL instead of the value you’re looking for.  If you find yourself in that situation, you should be able to use the code below to get the desired results:


 


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;


 


DECLARE @Command nvarchar(512)


SET @Command = N‘SELECT MAX(‘ + @TableName + ‘.’ + @IdentityColumn +


      N‘) FROM ‘ + @TableName + ‘ WITH (XLOCK)’;


 


BEGIN TRANSACTION


 


EXEC sp_executesql @Command


 


COMMIT TRANSACTION


 


Just create a function or stored procedure with the code above, and pass in the table name and the numeric column of your choice.


 

Comments (3)

  1. Siderite says:

    This works in SQL 2005 only, doesn’t it?

  2. irenak says:

    I believe ident_current is available in sql 2000, but not in sql 7

  3. chauhankamal says:

    Where is the information stored or maintained in the database, returned by IDENT_CURRENT function?

    Can we modify the vaue returned by this function?