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.
