InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:29:12 GMT20http://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxHad 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)Thu, 08 Apr 2010 06:00:11 GMThrodberahthttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxGood info...thanks....Wed, 21 May 2008 08:45:22 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxThanks 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:[code]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 TEST1DBCC CHECKIDENT('TEST1', RESEED, 1)-- verify the IDENT_CURRENT values are identicalSELECT IDENT_CURRENT('TEST1') AS Test1_IdentCurrent, IDENT_CURRENT('TEST2') AS Test2_IdentCurrentINSERT INTO TEST1 (OtherField) VALUES (1)INSERT INTO TEST2 (OtherField) VALUES (1)-- verify that the initial identity values are differentSELECT MIN(Rec_Num) AS Test1_FirstIdent FROM TEST1SELECT MIN(Rec_Num) AS Test2_FirstIdent FROM TEST2DROP TABLE dbo.TEST1DROP TABLE dbo.TEST2[/code]Results (abbreviated):[code]Test1_IdentCurrent Test2_IdentCurrent------------------ ------------------1 1Test1_FirstIdent----------------2Test2_FirstIdent----------------1[/code]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 MosesConEst Software SystemsWed, 21 May 2008 06:09:23 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxGood 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 [b]seed[/b] value of the identity column of [e.g. IDENTITY([b]5[/b],10)] the table. For all other cases, IDENT_CURRENT() shows the current identity value of the table.Tue, 20 May 2008 15:16:40 GMTRaghuram (AJ)http://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxThat'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 MosesConEst Software SystemsTue, 20 May 2008 14:01:55 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxVinay,NORESEED gives the info about identity values in the table. When you issueDBCC CHECKIDENT('table_name',NORESEED);You get the result which looks like this:Checking identity information: current identity value [i]value[/i], current column [i]value [/i]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:[code]CREATE TABLE dbo.Test_NoReseed (col1 INT IDENTITY(1,5), col2 VARCHAR(10))[/code]Let's check the CIV & CCV values:[code]DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)--Result[i]Checking identity information: current identity value 'NULL', current column value 'NULL'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/i][/code]Note the CIV & CCV values are NULL. Now let's insert 2 rows and view the table:[code]INSERT INTO dbo.Test_NoReseed (col2) VALUES ('One');INSERT INTO dbo.Test_NoReseed (col2) VALUES ('Two');SELECT * FROM dbo.Test_NoReseedcol1 col2----------- ----------1 One6 Two(2 row(s) affected)[/code]Now, let's check the CIV & CCV[code]DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)[i]Checking identity information: current identity value '6', current column value '6'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/i][/code]In this case, CCV is 6 because the maximum value of the identity column in the table is 6.Let's RESEED to 0:[code]DBCC CHECKIDENT('dbo.Test_NoReseed',RESEED,0)[/code]Now let's issue NORESEED and check the CIV & CCV values:[code]DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)[i]Checking identity information: current identity value '0', current column value '6'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/i][/code]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 tableNow let's insert a few rows & then CCV & CIV:[code]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_NoReseedcol1 col2----------- ----------1 One6 Two5 Three10 Four15 Five(5 row(s) affected)[/code]The CIV is 15 & CCV is 15:[code]DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)[i]Checking identity information: current identity value '15', current column value '15'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/i][/code]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:[code]DELETE FROM dbo.Test_NoReseed WHERE col1 = 15SELECT * FROM dbo.Test_NoReseedcol1 col2----------- ----------1 One6 Two5 Three10 Four(4 row(s) affected)DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)[i]Checking identity information: current identity value '15', current column value '15'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/i][/code]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:[code]DBCC CHECKIDENT('dbo.Test_NoReseed',RESEED,0)DBCC CHECKIDENT('dbo.Test_NoReseed',NORESEED)[i]Checking identity information: current identity value '0', current column value '10'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/i][/code]Note that after RESEED, the CCV is 10, the current maximum identity value of the table.Tue, 20 May 2008 13:49:38 GMTRaghuram (AJ)http://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxAll,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.Tue, 20 May 2008 09:28:12 GMTRaghuram (AJ)http://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxAJ, 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.Tue, 20 May 2008 09:03:54 GMTKit Brandnerhttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxSorry! it's not working..Can any one tell me differnece between "NORESEED" and "SEED" WITH Example.?thx in advance...VinayKTue, 20 May 2008 07:47:40 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxThis 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 MosesConEst Software SystemsTue, 20 May 2008 06:17:29 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxNice one AJ.Tue, 20 May 2008 05:45:52 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxFrom 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.Tue, 20 May 2008 03:36:13 GMTkrzysztof-202150http://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxI also got no discrepiancy while testing in 2005 & 2000.in both the cases after truncating the table the seed started with 0.Tue, 20 May 2008 03:04:40 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxHi,I have implemented the same thing but there was no discrepancy i mean after executing following command with TruncateDbcc CheckIdent('dbo.reseedtest','RESeed',0) the result was same for with SQL 200 and SQL 2005.Tue, 20 May 2008 01:37:10 GMTSunil Kumar-659768http://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxgood thing to know ...thnx! :hehe:Tue, 20 May 2008 00:39:49 GMTDugihttp://www.sqlservercentral.com/Forums/Topic503309-1294-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/RESEED/62847/">RESEED in SQL 2000 and SQL 2005/2008</A>[/B]Tue, 20 May 2008 00:02:48 GMTRaghuram (AJ)