Checking in Script if Identity has be nused

Copper Contributor


Is it possible to check in a SQL-Script if a Identity of a Table has ben used before?


My reason for asking is, that I need to reset the Seed of a Table, witch as dokumentet works with 'CHECKIDENT(Table, Ressed, [ID])', but the Problem with that is, is that depending if anything has been written into the table before, then then next ID id either [ID] or [ID]+1.

If I reset the Table with truncate, then the next ID is [ID], but if I only delete the Data, then the next ID is [ID]+1 when there was Data before and  [ID] if there was none since creation/truncate.

I can check with IDENT_CURRENT(table), and IDENT_SEED(table), but neither of them tell me if the identity has ben used.

The only way I found it to use 'dbcc CHECKIDENT(table, noreseed)', but this only returns the current value into the output, and I can't use this in a script.


So, if the table is new, or trancated, then  'dbcc CHECKIDENT(table, noreseed)' returns that the current identity is NULL, but 'select IDENT_CURRENT(table)' returns 0.

If I write one line into the table and delete it again, then  'dbcc CHECKIDENT(table, noreseed)' returns the last id, same as 'select IDENT_CURRENT(table)'



Acording to the documendation(IDENT_CURRENT (Transact-SQL) - SQL Server | Microsoft Docs) IDENT_CURRENT should return null if the table was just created or truncated, but it returned 0 for all my tests on sql server version 2014 till 2019.


I attached a zipped samplescript.

2 Replies

@Alexander Wurzinger 

Seems there is no other way to get this info;
CHECKIDENT does not work as documented; and it is not only issue with it. (There were complains that it is not thread-save/transaction-safe).

You would need to shop workarounds,
1) Use a PowerShell script where you can capture and parse DBCC output.
2) Always use truncate instead of check.
3) Consider to use SEQUENCE instead.

Thanks for the answer, but sadly neither of the 3 is a option for the thing I wanted to do.
It would also work if there was a sure/reliable way to get the next ID, but I didn't find any.