March 3, 2009 at 12:17 am
Comments posted to this topic are about the item Identity puzzle
March 3, 2009 at 1:08 am
Thanks! Some times sql behave in unexpected manner.
March 3, 2009 at 2:50 am
Nice question
March 3, 2009 at 8:16 am
Okay, I'm lost. If RESEED sets the value back to zero and this table was RESEEDed right before both queries, then why wouldn't both queries return zero?
March 3, 2009 at 8:35 am
The result depends on whether or not any rows have been inserted into the table since it was created. If there haven't (like in the first reseed), the next value inserted is the reseed value. If there have, then the next value inserted is the reseed + 1. Interestingly enough, this ignores the “increment” value of the identity column. If you TRUNCATE the table instead of deleting the rows, the table acts as if there have been no rows added and the reseed uses the reseed value again. It is odd behavior, but it is also documented in BOL.
Thanks,
Chad
March 3, 2009 at 8:53 am
Am I looking at the same question you are ?
There is no TRUNCATE TABLE anywhere in the code!
🙁
* Noel
March 3, 2009 at 8:56 am
Nevermind, I think I am in need of coffee 😉
* Noel
March 3, 2009 at 8:59 am
Chad - got it. Thanks!
March 3, 2009 at 9:32 am
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.
March 3, 2009 at 3:36 pm
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)
March 3, 2009 at 4:34 pm
Hmmm, that bonus question has made it kind of quiet around here until Tim came through.
Tim Wilson-Brown
The answer to the bonus question is to use DBCC CHECKIDENT without reseeding
This will work if you can catch the dbcc output. The bonus bonus question: how do we interrogate the 'dbcc checkindent' output in tsql.
Raymond
March 3, 2009 at 9:57 pm
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?
March 3, 2009 at 10:21 pm
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...
March 4, 2009 at 8:56 am
Thank you, Tim. You get the bonus points and the bonus bonus points.
Here's my quick tsql for wrapping this up. This is still more work than I want to do and test, so your option b of always truncating before reseed is straight forward and just as consistent.
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
mondo
March 4, 2009 at 9:20 am
Got 3 points ! ! ! What a fluke. But I still was not able to understand reseed issue.
SQL DBA.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply