Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Have you seen the syntax of DBCC CHECKIDENT? Change that 0 to 1 and you're set. –  Marian Feb 26 '13 at 11:52
    
Thanks - But I am calling a delete before dbcc checkident. As far as I know this should reseed from newvalue + 1. Moreover why does Identity(1,1) not work? –  gunnerz Feb 26 '13 at 12:06
    
The delete doesn't reseed the value. Only TRUNCATE does the reseed. –  Marian Feb 26 '13 at 12:54

2 Answers 2

up vote 6 down vote accepted

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 is acknowledged as a bug, 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;

Id 1

share|improve this answer
    
Thanks.However can you explain why I need to reseed though. Why does specifying Identity(1,1) not start the first record from 1. –  gunnerz Feb 26 '13 at 12:49

This is happening because you are using 0 for you the new_reseed_value in your DBCC CHECKIDENT command.

See this section from DBCC CHECKIDENT

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.

share|improve this answer
    
Thanks. yes I think this was what I doing wrong. However can you explain why I need to reseed though. Why does specifying Identity(1,1) not start the first record from 1. –  gunnerz Feb 26 '13 at 12:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.