Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RESEED in SQL 2000 and SQL 2005/2008


RESEED in SQL 2000 and SQL 2005/2008

Author
Message
Raghuram (AJ)
Raghuram (AJ)
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 299
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! Smile ]

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
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
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. Smile

Ron Moses
ConEst Software Systems

-----
a haiku...

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

Raghuram (AJ)
Raghuram (AJ)
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 299
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
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
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

Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6267 Visits: 1407
Good info...thanks....



hrodberaht
hrodberaht
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 24
Had similar issues with reseeding "virgin tables". Key is that last_value in Sys.Idnetity_Columns is "null" for a virgin table, and not zero as one would have thought. The script below solved my issue:

declare @TableName nvarchar(123)
set @TableName = 'test'

IF NOT EXISTS(select *
          FROM SYS.IDENTITY_COLUMNS
          JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
          WHERE SYS.TABLES.Name = @TableName AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL)
   DBCC CHECKIDENT (@TableName, RESEED, 0)


Robert
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search