Tim Wilson-Brown

The answer to the bonus question is to use DBCC CHECKIDENT without reseeding

Tim Wilson-Brown (3/3/2009)

---

I'll leave you with the challenge of parsing the output from DBCC CHECKIDENT.

brewmanz (3/3/2009)

---

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

CREATE TABLE id_test ( my_id INT IDENTITY(1,1) NOT NULL)

INSERT INTO id_test DEFAULT VALUES;
INSERT INTO id_test DEFAULT VALUES;

-- uncomment delete or truncate to test the two scenarios
DELETE FROM id_test

--truncate TABLE id_test

IF EXISTS( SELECT 1
FROM sys.identity_columns i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'id_test' AND i.last_value IS not NULL )
DBCC CHECKIDENT(id_test, RESEED, 0)
ELSE
DBCC CHECKIDENT(id_test, RESEED, 1)

INSERT INTO id_test DEFAULT VALUES;

SELECT * FROM id_test

DROP TABLE id_test

go