x

Identity Value reseed did not reseed!

Hi,

I have a small problem. I was rolling out new changes to our production database. This rollout required me to delete a few tables entirely, reseed them with a "zero" value and when the tables would get repopulated, the identity values would restart from zero. However, that did not happen all my tables have different identity values (depending on what was the last identity value before they were entirely deleted). For example, table A has the first identity value of 6437 and it continues sequentially.

Table B for example starts with 335 identity value now and so on.

What didnt my reseed script work? Please see below my code for deleting & reseeding.

DELETE FROM [Database].[dbo].[TableAAAAAA]
DBCC CHECKIDENT ([TableAAAAAA], reseed, 0)

DELETE FROM [Database].[dbo].[TableBBBBB]
DBCC CHECKIDENT ([TableBBBBB], reseed, 0)

DELETE FROM [Database].[dbo].[TableCCCCC]
DBCC CHECKIDENT ([TableCCCCC], reseed, 0)

Using SQL Server 2005 Standard Edition.

Thanks, S

more ▼

asked Dec 16, 2009 at 01:50 PM in Default

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I would suggest TRUNCATE table rather then DELETE.

I beleive it is a flaw/limitation in DBCC CHECKIDENT.


I think this is ourr explanation...

From Books Online:

Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or 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.

more ▼

answered Dec 16, 2009 at 02:00 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

It was because of constraints we couldnt do a truncate. How do I explain this flaw/limitation to other people though. That's the hardest part because they think it should just work.
Dec 16, 2009 at 02:02 PM Slick84
A delete assumes that data remains and new inserts will happen... truncate clears all data and assumes a refresh. May not be 100% technical but it is an explanation ;)
Dec 16, 2009 at 02:16 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

I don't think that the issue has to do with delete v. truncate. I created the following test script, which can't use the truncate due to the FK constraint to mimic the OP.

I ran this, it worked and reseeded the table correctly:

SET NOCOUNT ON;
GO

CREATE TABLE Test1 
(
    ID INT IDENTITY(1,1)
    ,MyValue INT
)
GO

ALTER TABLE Test1 
    ADD CONSTRAINT PK_ID PRIMARY KEY (ID);
GO

CREATE TABLE Test2
(
    ID	INT NOT NULL
    ,SomeOtherValue INT
)

ALTER TABLE Test2 
    ADD CONSTRAINT PK_ID2 PRIMARY KEY (ID);
GO

ALTER TABLE Test2 
    ADD CONSTRAINT FK_ID FOREIGN KEY (ID)
    REFERENCES Test1 (ID);
GO


INSERT INTO Test1(MyValue) VALUES (9)
GO 1000

SELECT * FROM Test1
GO

DELETE FROM Test1
GO

DBCC CHECKIDENT(Test1, RESEED,0)
GO

INSERT INTO Test1 (MyValue) VALUES (8)
GO

SELECT * FROM Test1
GO

DROP TABLE Test2
GO
DROP TABLE Test1
GO

This was the dbcc output:

Beginning execution loop Batch execution completed 1000 times. Checking identity information: current identity value '1000', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

more ▼

answered Dec 16, 2009 at 05:48 PM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

(comments are locked)
10|1200 characters needed characters left

Just a thought - Did you run those statements in the correct database?

You may have deleted from database 'Database', but the reseed would be run in the current database - which may be different!

more ▼

answered Dec 16, 2009 at 02:02 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.2k 47 49 76

I made a script and used the "USE DATABASE" statement on top. Good catch though because I thought about this too.
Dec 16, 2009 at 02:22 PM Slick84
(comments are locked)
10|1200 characters needed characters left

Please try with this code.

Run it step by step. You neet to disble identity column for a moment.

-- Create test table CREATE TABLE Test ( id int identity, obs varchar(50) )

--Verify the identity column INSERT INTO Test (obs) VALUES ('Probando id') GO --Look at the id value SELECT * FROM Test ORDER BY id GO --Now we need to insert more values for id INSERT INTO Test (obs) VALUES ('Se inserta del #2 al #51') GO 50 --Look at the id value SELECT * FROM Test ORDER BY id GO --DELETE 10 records of the table DELETE FROM test WHERE id between 30 and 40 GO -- Number of records 51 - 10 SELECT COUNT(*) FROM Test GO -- id's 30 to 40 were deleted SELECT * FROM Test ORDER BY id GO -- verify the identity actual value DBCC CHECKIDENT ("Test", NORESEED); GO -- Disable identity insert /*******************************/ set identity_insert Test on GO -- INSERT id values 30 and 31 INSERT INTO Test (id,obs) VALUES (30,'Se inserta del #2 al #51') GO INSERT INTO Test (id,obs) VALUES (31,'Se inserta del #2 al #51') GO -- Enable identity value set identity_insert Test off GO -- verify records SELECT * FROM Test ORDER BY id GO -- verify value for the identity column INSERT INTO Test (obs) VALUES ('Probando id #52') GO
more ▼

answered Dec 16, 2009 at 07:29 PM

Carlos SFC gravatar image

Carlos SFC
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1950
x987
x47
x34

asked: Dec 16, 2009 at 01:50 PM

Seen: 3126 times

Last Updated: Dec 16, 2009 at 01:50 PM