Reseed a newly created table

I have create a new table on SQL Server 2008.

This table has an identity column, defined like this:

[ID] [int] IDENTITY(1,1) NOT NULL

However when I insert any data, the first record starts from 0, instead of 1.

I want the records to start from 1. Why does this not work?

If I delete the record and reseed it to 0, it works.

Delete from [tablename]
DBCC CHECKIDENT('[tablename]', RESEED, 0)

However it does not work on newly created tables.

Even if I delete and reseed a newly created table, it still starts adding record from 0, instead of 1.

Can you anyone please suggest what I am doing wrong.

Thanks

This behaviour is documented, but counter-intuitive:


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.

It was acknowledged as a bug (on the now-defunct Microsoft Connect site), but not one that will be fixed. Workarounds include avoiding using the RESEED option on newly-created tables, or to use a higher value that will work in all cases but may result in gaps. Gaps are all but inevitable with IDENTITY anyway. If you must do the reseed, wrap it in a check like:

IF EXISTS 
(
    SELECT 1 
    FROM sys.identity_columns 
    WHERE 
        [object_id] = OBJECT_ID(N'dbo.T1', N'U')
        AND last_value IS NOT NULL
)
BEGIN
    DBCC CHECKIDENT('dbo.T1', 'RESEED', 0)
END;

To be clear, creating a table and adding a row will result in the first identity value being used:

-- Tested on SQL Server 2008 SP3 CU8
-- build 10.0.5828
CREATE TABLE dbo.T1 (id int IDENTITY(1, 1));
INSERT dbo.T1 DEFAULT VALUES;
SELECT id FROM dbo.T1 AS t;

StackExchange Database Administrators Q#35443, answer score: 8