September 10, 2012 at 8:59 am
Hi
I'm not sure if I misunderstand something. I use CHECKIDENT to reset the value of a IDENTITY column to "1". The thing I don't get is:
Here is a sample that demonstrates what I mean:
USE tempdb;
GO
CREATE TABLE Foo (
Id INT NOT NULL IDENTITY(1,1)
,Num INT
);
GO
DELETE FROM Foo;
GO
DBCC CHECKIDENT (Foo, RESEED, 0);
GO
INSERT INTO Foo VALUES (1);
GO
SELECT * FROM Foo;
GO
DELETE FROM Foo;
GO
DBCC CHECKIDENT (Foo, RESEED, 0);
GO
INSERT INTO Foo VALUES (1);
GO
SELECT * FROM Foo;
GO
DROP TABLE Foo;
This is not an issue for any project here but I'd like to understand the results.
Thanks
Flo
September 10, 2012 at 12:25 pm
Florian! Long time no see!
Anyhow, as I recall, this is known behavior, though I forget right now where Microsoft documented it...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2012 at 12:29 pm
OK, here's the quote from the documnetation:
Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
This is the 2012 doc at http://msdn.microsoft.com/en-us/library/ms176057.aspx?queryresult=true
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply