SQL reseeding works but auto increment starts at 0

Question

I have some table that have an identity column that I am trying to reseed. The reseeding works (I think) but when a new data item is inserted into the table the identity column starts at 0.

My code to reseed is:

DBCC CHECKIDENT(MyTable, RESEED, 0)

The Identity Specifications for the tables are:

• Identity Increment = 1
• Identity Seed = 1

QUICK NOTE I am performing a delete on the tables prior to reseeding

Please help

Solution 1

It appears that "uninitialized" or truncated tables (meaning no data has ever been inserted into the table) when the seed is initialized to 0 will start at 0. But when data has been inserted into a table and a delete is used to clear all rows of the table. Reseeding to 0 will only leave the database's last seed at 0 with the next seed being 1.

That said here is an example replicating the issue:

-- Script to create a test table
IF EXISTS(SELECT 1 FROM Information_Schema.Tables WHERE TABLE_SCHEMA = 'dbo' 
            AND TABLE_NAME = 'SeedTest') BEGIN

    DROP TABLE SeedTest

END

-- Create a Test Seed Table
CREATE TABLE [dbo].[SeedTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](255) NOT NULL,
 CONSTRAINT [PK_SeedTest] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- When a table is truncated or "Not Initialized" (meaning no data EVER inserted)
-- An initial reseed of 0 will make the first identity insert value = 0.
DBCC CHECKIDENT (SeedTest, RESEED, 0)

GO

INSERT INTO SeedTest([Value]) VALUES('Test')
SELECT * FROM SeedTest

GO

-- If you truncate the table and reseed the same effect will occur (first identity insert value = 0).
TRUNCATE TABLE SeedTest

GO

DBCC CHECKIDENT (SeedTest, RESEED, 0)

GO

INSERT INTO SeedTest([Value]) VALUES('Test')

SELECT * FROM SeedTest


-- When Deleting records from a table (Foreign key constraints may prevent a truncate)
-- Reseeding to 0 will set the last seed to 0 and make the next seed = 1
DELETE FROM SeedTest

GO

DBCC CHECKIDENT (SeedTest, RESEED, 0)

GO

INSERT INTO SeedTest([Value]) VALUES('Test')
SELECT * FROM SeedTest

GO

Solution 2

I think what you are looking for is:

DBCC CHECKIDENT(MyTable, RESEED, -1)

This is only if the Table has been incremented, that is row had been added and then deleted. The next inserted row will now be incremented giving 0.

In the case the table didn't have rows, use: DBCC CHECKIDENT(MyTable, RESEED, 0)

Solution 3

Using that command, you're telling the IDENTITY to set itself back to 0 as its new seed.

It will not go back to the original definition (IDENTITY(1,1)), but to the value you specify as the third parameter in the DBCC command.

If you want to go back to using a 1 as your seed value, use:

DBCC CHECKIDENT(MyTable, RESEED, 1)

If you want to go to 100, use:

DBCC CHECKIDENT(MyTable, RESEED, 100)

That value that you defined and set by using DBCC CHECKIDENT will then be the first new value used for the IDENTITY column when you insert a row into that table.

When you check the MSDN Books Online documentation, you can see:

DBCC CHECKIDENT 
( 
    table_name
        [ , { NORESEED | { RESEED [ ,new_reseed_value ] } } ]
)

new_reseed_value

Is the new value to use as the current value of the identity column.

So it's really you who defines the new value of the IDENTITY column - if you pass in 0 as you do in your post, it will be 0 - that's what you asked for, after all...