RESEED in SQL 2000 and SQL 2005/2008

  • Comments posted to this topic are about the item RESEED in SQL 2000 and SQL 2005/2008

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • good thing to know ...thnx! :hehe:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Hi,

    I have implemented the same thing but there was no discrepancy i mean after executing following command with Truncate

    Dbcc CheckIdent('dbo.reseedtest','RESeed',0) the result was same for with SQL 200 and SQL 2005.

  • I also got no discrepiancy while testing in 2005 & 2000.

    in both the cases after truncating the table the seed started with 0.

    "Keep Trying"

  • From my experience the behaviour in both SQL 2000 and 2005 is exactly the same.

    A few years ago I was actually surprised that I was getting different results when calling RESEED on brand new table as opposed to the table that had some date previously inserted and then deleted. And that was when I was using SQL 2000.

  • Nice one AJ.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • This has been a headache for me, not knowing whether a table is going to reseed at 1 (virgin) or 2. Every technique I've tried to determine the "virginity" of a table has failed, or provided inconsistent results. Fortunately for me, it's not a deal-breaker in our system -- none of our identity values are visible to the user so it really doesn't matter if the first record comes in as 2 instead of 1. But the anal-retentive in me isn't very happy about it.

    Ron Moses

    ConEst Software Systems

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Sorry! it's not working..

    Can any one tell me differnece between "NORESEED" and "SEED" WITH Example.?

    thx in advance...

    VinayK

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • AJ, could the problem be in your script? I haven't tried the code myself, but the original table definition has an identity seed of 1, and the RESEED command used later on has a seed value of 0.

  • All,

    My bad.

    It could be my script...coz I tested a similar scenario on fellow colleagues' machines with successful results and only then did I decide to write the article. While I find out what gave me different results...I agree that the behavior is same for both 2000 & 2005/2008. My search continues as to what changed between then & now.

    A few found the virgin tables & truncate's effect on identity useful...that's a mild consolation.

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • Vinay,

    NORESEED gives the info about identity values in the table. When you issue

    DBCC CHECKIDENT('table_name',NORESEED);

    You get the result which looks like this:

    Checking identity information: current identity value value, current column value

    Current Identity Value (CIV) is self explanatory. Current column value (CCV) specifies the highest identity value present in the table. For a non-gapped & non reseeded table, both CIV & CCV are the same.

    For a virgin table, the CIV & CCV are NULL. Let's see the results of NORESEED at various stages with an example: [This works, I promise! 🙂 ]

    Let's create a table:

    CREATE TABLE dbo.Test_NoReseed (col1 INT IDENTITY(1,5), col2 VARCHAR(10))

    Let's check the CIV & CCV values:

    DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)

    --Result

    Checking identity information: current identity value 'NULL', current column value 'NULL'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Note the CIV & CCV values are NULL. Now let's insert 2 rows and view the table:

    INSERT INTO dbo.Test_NoReseed (col2) VALUES ('One');

    INSERT INTO dbo.Test_NoReseed (col2) VALUES ('Two');

    SELECT * FROM dbo.Test_NoReseed

    col1 col2

    ----------- ----------

    1 One

    6 Two

    (2 row(s) affected)

    Now, let's check the CIV & CCV

    DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)

    Checking identity information: current identity value '6', current column value '6'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    In this case, CCV is 6 because the maximum value of the identity column in the table is 6.

    Let's RESEED to 0:

    DBCC CHECKIDENT('dbo.Test_NoReseed',RESEED,0)

    Now let's issue NORESEED and check the CIV & CCV values:

    DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)

    Checking identity information: current identity value '0', current column value '6'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Note the CIV is 0, due to the reseed and the CCV is 6 which is the maximum value of the identity column in the table

    Now let's insert a few rows & then CCV & CIV:

    INSERT INTO dbo.Test_NoReseed (col2) VALUES ('Three');

    INSERT INTO dbo.Test_NoReseed (col2) VALUES ('Four');

    INSERT INTO dbo.Test_NoReseed (col2) VALUES ('Five');

    SELECT * FROM dbo.Test_NoReseed

    col1 col2

    ----------- ----------

    1 One

    6 Two

    5 Three

    10 Four

    15 Five

    (5 row(s) affected)

    The CIV is 15 & CCV is 15:

    DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)

    Checking identity information: current identity value '15', current column value '15'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Now, if even if you delete the row that has col1 as 15, the value of CCV remains 15. However, if you issue a RESEED to 0 again, the CCV takes up the then maximum identity value in the table:

    DELETE FROM dbo.Test_NoReseed WHERE col1 = 15

    SELECT * FROM dbo.Test_NoReseed

    col1 col2

    ----------- ----------

    1 One

    6 Two

    5 Three

    10 Four

    (4 row(s) affected)

    DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)

    Checking identity information: current identity value '15', current column value '15'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Note that CCV is still 15 even though it doesn't exist in the table. Another normal insert would then take the new max dentity value.

    Now let's reseed & recheck the values:

    DBCC CHECKIDENT('dbo.Test_NoReseed',RESEED,0)

    DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)

    Checking identity information: current identity value '0', current column value '10'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Note that after RESEED, the CCV is 10, the current maximum identity value of the table.

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • That's useful info, AJ.

    Forgive me if this is an obvious question, but is there a way to get those values in script so they can be used in a conditional? For example, if you run DBCC CHECKIDENT('dbo.Table',RESEED,1) on a virgin table, your first record will get an identity value of 1. Delete all records from a non-virgin table and run the same command, and you'll get an initial identity of 2. So let's say you want to strip and reseed a table of indeterminate virginity, and for whatever reason truncating isn't an option. What I'm wondering is how to run...

    DBCC CHECKIDENT('dbo.Table',RESEED, (CASE WHEN CIV IS NULL THEN 1 ELSE 0 END))

    Forgive me if I've said anything stupid, I'm still learning. 🙂

    Ron Moses

    ConEst Software Systems

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Good question Ron.

    There's a function IDENT_CURRENT(table_name) that gives the current identity value of the the table. I am not sure if it can be included in the DBCC statement itself...but you could declare it to a variable & use it. E.g.:

    DECLARE @ReseedValue INT;

    SELECT @ReseedValue = CASE WHEN IDENT_CURRENT('table_name') = 10 THEN 0 ELSE 1 END;

    DBCC CHECKIDENT(table_name',RESEED,@ReseedValue);

    Note: When the NORESEED shows CIV as NULL, IDENT_CURRENT will show the seed value of the identity column of [e.g. IDENTITY(5,10)] the table. For all other cases, IDENT_CURRENT() shows the current identity value of the table.

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • Thanks AJ. I have tried using IDENT_CURRENT() but what I found is that it returns the same result on both a virgin table and a non-virgin table that has previously been stripped and reseeded. Case in point:

    CREATE TABLE dbo.TEST1 (Rec_Num int IDENTITY(1,1) PRIMARY KEY, OtherField int)

    CREATE TABLE dbo.TEST2 (Rec_Num int IDENTITY(1,1) PRIMARY KEY, OtherField int)

    INSERT INTO TEST1 (OtherField) VALUES (1)

    DELETE FROM TEST1

    DBCC CHECKIDENT('TEST1', RESEED, 1)

    -- verify the IDENT_CURRENT values are identical

    SELECT IDENT_CURRENT('TEST1') AS Test1_IdentCurrent,

    IDENT_CURRENT('TEST2') AS Test2_IdentCurrent

    INSERT INTO TEST1 (OtherField) VALUES (1)

    INSERT INTO TEST2 (OtherField) VALUES (1)

    -- verify that the initial identity values are different

    SELECT MIN(Rec_Num) AS Test1_FirstIdent FROM TEST1

    SELECT MIN(Rec_Num) AS Test2_FirstIdent FROM TEST2

    DROP TABLE dbo.TEST1

    DROP TABLE dbo.TEST2

    Results (abbreviated):

    Test1_IdentCurrent Test2_IdentCurrent

    ------------------ ------------------

    1 1

    Test1_FirstIdent

    ----------------

    2

    Test2_FirstIdent

    ----------------

    1

    Note that both tables return an IDENT_CURRENT of 1, but generate different identity values on the next insert. I imagine I'm probably missing some obvious step that would work around this.

    Ron Moses

    ConEst Software Systems

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Good info...thanks....

Viewing 15 posts - 1 through 15 (of 15 total)

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