Identity puzzle

  • Comments posted to this topic are about the item Identity puzzle

  • Thanks! Some times sql behave in unexpected manner.

  • Nice question

  • 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?

  • 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

  • Am I looking at the same question you are ?

    There is no TRUNCATE TABLE anywhere in the code!

    ๐Ÿ™


    * Noel

  • Nevermind, I think I am in need of coffee ๐Ÿ˜‰


    * Noel

  • Chad - got it. Thanks!

  • 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.

  • 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)

  • 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

  • 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?

  • 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...

  • 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

  • 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 18 total)

You must be logged in to reply to this topic. Login to reply