Checking in Script if Identity has be nused

%3CLINGO-SUB%20id%3D%22lingo-sub-2565456%22%20slang%3D%22de-DE%22%3EChecking%20in%20Script%20if%20Identity%20has%20be%20nused%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2565456%22%20slang%3D%22de-DE%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20check%20in%20a%20SQL-Script%20if%20a%20Identity%20of%20a%20Table%20has%20ben%20used%20before%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20reason%20for%20asking%20is%2C%20that%20I%20need%20to%20reset%20the%20Seed%20of%20a%20Table%2C%20witch%20as%20dokumentet%20works%20with%20'CHECKIDENT(Table%2C%20Ressed%2C%20%5BID%5D)'%2C%20but%20the%20Problem%20with%20that%20is%2C%20is%20that%20depending%20if%20anything%20has%20been%20written%20into%20the%20table%20before%2C%20then%20then%20next%20ID%20id%20either%20%5BID%5D%20or%20%5BID%5D%2B1.%3C%2FP%3E%3CP%3EIf%20I%20reset%20the%20Table%20with%20truncate%2C%20then%20the%20next%20ID%20is%20%5BID%5D%2C%20but%20if%20I%20only%20delete%20the%20Data%2C%20then%20the%20next%20ID%20is%20%5BID%5D%2B1%20when%20there%20was%20Data%20before%20and%20%5BID%5D%20if%20there%20was%20none%20since%20creation%2Ftruncate.%3C%2FP%3E%3CP%3EI%20can%20check%20with%20IDENT_CURRENT(table)%2C%20and%20IDENT_SEED(table)%2C%20but%20neither%20of%20them%20tell%20me%20if%20the%20identity%20has%20ben%20used.%3C%2FP%3E%3CP%3EThe%20only%20way%20I%20found%20it%20to%20use%20'dbcc%20CHECKIDENT(table%2C%20noreseed)'%2C%20but%20this%20only%20returns%20the%20current%20value%20into%20the%20output%2C%20and%20I%20can't%20use%20this%20in%20a%20script.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20if%20the%20table%20is%20new%2C%20or%20trancated%2C%20then%20'dbcc%20CHECKIDENT(table%2C%20noreseed)'%20returns%20that%20the%20current%20identity%20is%20NULL%2C%20but%20'select%20IDENT_CURRENT(table)'%20returns%200.%3C%2FP%3E%3CP%3EIf%20I%20write%20one%20line%20into%20the%20table%20and%20delete%20it%20again%2C%20then%20'dbcc%20CHECKIDENT(table%2C%20noreseed)'%20returns%20the%20last%20id%2C%20same%20as%20'select%20IDENT_CURRENT(table)'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemark%3A%3C%2FP%3E%3CP%3EAcording%20to%20the%20documendation(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fident-current-transact-sql%3Fview%3Dsql-server-ver15%26amp%3BviewFallbackFrom%3Dsql-server-2019%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EIDENT_CURRENT%20(Transact-SQL)%20-%20SQL%20Server%20%7C%20Microsoft%20Docs%3C%2FA%3E)%20IDENT_CURRENT%20should%20return%20null%20if%20the%20table%20was%20just%20created%20or%20truncated%2C%20but%20it%20returned%200%20for%20all%20my%20tests%20on%20sql%20server%20version%202014%20till%202019.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20a%20zipped%20samplescript.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570082%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20in%20Script%20if%20Identity%20has%20be%20nused%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F65199%22%20target%3D%22_blank%22%3E%40Alexander%20Wurzinger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20there%20is%20no%20other%20way%20to%20get%20this%20info%3B%3CBR%20%2F%3ECHECKIDENT%20does%20not%20work%20as%20documented%3B%20and%20it%20is%20not%20only%20issue%20with%20it.%20(There%20were%20complains%20that%20it%20is%20not%20thread-save%2Ftransaction-safe).%3C%2FP%3E%3CP%3EYou%20would%20need%20to%20shop%20workarounds%2C%3CBR%20%2F%3E1)%20Use%20a%20PowerShell%20script%20where%20you%20can%20capture%20and%20parse%20DBCC%20output.%3CBR%20%2F%3E2)%20Always%20use%20truncate%20instead%20of%20check.%3CBR%20%2F%3E3)%20Consider%20to%20use%20SEQUENCE%20instead.%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlshack.com%2Fdifference-between-identity-sequence-in-sql-server%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlshack.com%2Fdifference-between-identity-sequence-in-sql-server%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.