May 20, 2008 at 12:02 am
Comments posted to this topic are about the item RESEED in SQL 2000 and SQL 2005/2008
May 20, 2008 at 12:39 am
May 20, 2008 at 1:37 am
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.
May 20, 2008 at 3:04 am
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"
May 20, 2008 at 3:36 am
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.
May 20, 2008 at 5:45 am
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
May 20, 2008 at 6:17 am
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
May 20, 2008 at 7:47 am
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 !!!
May 20, 2008 at 9:03 am
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.
May 20, 2008 at 9:28 am
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.
May 20, 2008 at 1:49 pm
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.
May 20, 2008 at 2:01 pm
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
May 20, 2008 at 3:16 pm
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.
May 21, 2008 at 6:09 am
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
May 21, 2008 at 8:45 am
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