SQL Server Identity Column Values Start at 0 Instead of 1

SQL server identity column values start at 0 instead of 1

From DBCC CHECKIDENT

DBCC CHECKIDENT ( table_name, RESEED, 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.

So, this is expected for an empty or truncated table.

SQL identity (1,1) starting at 0

I expect someone/something has run:

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0);

If you run the following:

CREATE TABLE dbo.MyTable(
    MyTableID int IDENTITY(1,1) NOT NULL,
    RecordName nvarchar(100) NULL
);

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0);
DBCC CHECKIDENT ('dbo.MyTable', NORESEED);

The second CHECKIDENT still returns NULL:

Checking identity information: current identity value 'NULL', current column value 'NULL'.

However the next identity value will be 0. This is documented behaviour, MSDN states:

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

This only works on newly created/truncated tables where the last_value column in sys.identity_columns is still NULL. As described above if you were to insert a row, delete it, then reseed to 0, the new identity would still be 1.

Full Test Script

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
    DROP TABLE dbo.T;

CREATE TABLE dbo.T(ID INT IDENTITY(1,1) NOT NULL);
INSERT dbo.T OUTPUT inserted.* DEFAULT VALUES;
-- OUTPUTS 1

DELETE dbo.T;
DBCC CHECKIDENT ('dbo.T', RESEED, 0);
INSERT dbo.T OUTPUT inserted.* DEFAULT VALUES;
-- OUTPUTS 1

TRUNCATE TABLE dbo.T;
DBCC CHECKIDENT ('dbo.T', RESEED, 0);
INSERT dbo.T OUTPUT inserted.* DEFAULT VALUES;
-- OUTPUTS 0

SQL primary key can accept '0'?

Primary Key Can be Zero, but if you set Identity on the column it normally will start at 1 rather than Zero.

Reseed identity seed to always start with 1 - practical code

You have 2 options, essentially.

You can go the way you suggested and insert dummy data after creation and then delete it, in which case DBCC CHECKIDENT ('TableName', RESEED, 0); will always work.

You can also refrain from deleting the rows beforehand and do something like this:

IF NOT EXISTS (SELECT 1 FROM TableName)
    BEGIN
        DBCC CHECKIDENT ('TableName', RESEED, 1);
    END
ELSE
    BEGIN
        DELETE * FROM TableName;
        DBCC CHECKIDENT ('TableName', RESEED, 0);
    END
GO

Reset any identity column to start with 1 as next value

Use if all tables have identity field.

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 1)'

MSforeachtable is an undocumented, but extremely handy stored proc which executes a given command against all tables in your database.

To reseed ONLY tables with an identity column you can use the next script.
It also makes use of sp_MSforeachtable but taking into account the correct tables.

EXEC sp_MSforeachtable '
IF (SELECT COUNT(1) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = ''BASE TABLE'' 
    AND ''[''+ TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' = ''?'' 
    AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ''TableHasIdentity'') = 1) > 0 
BEGIN
    DBCC CHECKIDENT (''?'', RESEED, 1)
END'

Use Following query to get Last Identity value inserted in tables in a database.

   SELECT TableName = OBJECT_NAME(OBJECT_ID) ,
       ColumnName = name ,
       OriginalSeed = seed_value ,
       Step = increment_value ,
       LastValue = last_value ,
       IsNotForReplication = is_not_for_replication
FROM sys.identity_columns

Refer this for more details

How do I start autoIncrement from 0 instead of 1 with Hibernate?

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.)

Reference

Reference 2

---

Related Topics