brewmanz (3/3/2009)
Tim Wilson-Brown (3/3/2009)
I'll leave you with the challenge of parsing the output from DBCC CHECKIDENT.SQL Server should act like a Finite State Machine, to be able to predict its behaviour when a known input is applied, such as DBCC CHECKIDENT (#a, RESEED, 0) and then a new record inserted.
Clearly, DBCC CHECKIDENT without RESEED is accessing some information that is most useful to us trying to predict the behaviour when a new record is inserted, but I for one cannot see how to do it (other than the clumsy parse of DBCC CHECKIDENT output).
Does anyone know how to directly get this information?
CREATE TABLE id_test ( my_id INT IDENTITY(1,1) NOT NULL)
SELECT i.last_value
FROM sys.identity_columns i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'id_test'
DBCC CHECKIDENT(id_test)
INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test
SELECT i.last_value
FROM sys.identity_columns i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'id_test'
DBCC CHECKIDENT(id_test)
INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test
DROP TABLE id_test
go
The fact that 'NULL' is returned by DBCC CHECKIDENT is a big clue...