raymond lew (3/3/2009)
I am glad to see so many people getting this right. And for 3 points!It certainly does not operate according to how I expect things to work. I just wished I had posted something more challenging than a simple script.;)
Perhaps something like:
Given a table defined as
CREATE TABLE id_test (my_id INT IDENTITY(1,1) NOT NULL)
and
(select count(*) from given_table) = 0
Select the right action to take to ensure that the next row inserted is 1
a) DBCC CHECKIDENT(id_test, RESEED, 0)
b) DBCC CHECKIDENT(id_test, RESEED, 1)
c) it depends, a) OR b) depending on the circumstance.
Bonus question:
If you select c, you are correct. Now how would you determine which reseed statement to perform?
I am really interested in the answer to the bonus question as I have not been able to crack this.
The answer to the bonus question is to use DBCC CHECKIDENT without reseeding 🙂
CREATE TABLE id_test ( my_id INT IDENTITY(1,1) NOT NULL)
DBCC CHECKIDENT(id_test)
INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test
DBCC CHECKIDENT(id_test)
INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test
DROP TABLE id_test
go
Returns
Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
(1 row(s) affected)
Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
(1 row(s) affected)
If DBCC CHECKIDENT (NORESEED) reports NULL for the current identity value, use 1.
Otherwise, if it reports 1, use 0.
CREATE TABLE id_test ( my_id INT IDENTITY(1,1) NOT NULL)
DBCC CHECKIDENT(id_test)
DBCC CHECKIDENT (id_test, RESEED, 1)--reseed to 1
INSERT INTO id_test DEFAULT VALUES;
select my_id FROM id_test --select number 1
DELETE FROM id_test
DBCC CHECKIDENT(id_test)
DBCC CHECKIDENT (id_test, RESEED, 0)--reseed to 0 + 1
INSERT INTO id_test DEFAULT VALUES;
select my_id FROM id_test --select number 2
DELETE FROM id_test
DROP TABLE id_test
go
I'll leave you with the challenge of parsing the output from DBCC CHECKIDENT.
Of course, there is also a trivial answer:
TRUNCATE TABLE id_test -- reset table to a known state
DBCC CHECKIDENT (id_test, RESEED, 1)