RESEED in SQL 2000 and SQL 2005/2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by AJ » RESEED in SQL 2000 and SQL 2005/2008

16 posts, Page 2 of 2

««1 2

RESEED in SQL 2000 and SQL 2005/2008

Rate Topic

Display Mode

Topic Options

Author

Message

Raghuram (AJ)

Posted Tuesday, May 20, 2008 1:49 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:53 PM
Points: 494, 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! :) ]

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

Post #504014

ronmoses

Posted Tuesday, May 20, 2008 2:01 PM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 649, Visits: 686

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

Post #504029

Raghuram (AJ)

Posted Tuesday, May 20, 2008 3:16 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:53 PM
Points: 494, 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

Post #504087

ronmoses

Posted Wednesday, May 21, 2008 6:09 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 649, Visits: 686

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

Post #504393

Anipaul

Posted Wednesday, May 21, 2008 8:45 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 4,787, Visits: 1,336

Good info...thanks....

Post #504527

hrodberaht

Posted Thursday, April 08, 2010 6:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 31, 2012 2:59 AM
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

Post #899448

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions