Checking in Script if Identity has be nused

New Contributor

Hi,

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)'

 

remark:

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.
https://www.sqlshack.com/difference-between-identity-sequence-in-sql-server/

@AnatoliD
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.