Why setting current identity value is not working for me in SQL Server 2008 R2?

Question

I am working with SQL Server 2008 R2.

I have a table seq_audit which has an identity column. This is the definition of the table:

CREATE TABLE [dbo].[seq_audit]
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [value] [bit] NULL,

    PRIMARY KEY CLUSTERED ([id] ASC)
          WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

The table is empty and never has had any rows in it before.

To check its current identity value, I ran this command:

DBCC CHECKIDENT (seq_audit, NORESEED) 
GO

And this is the result I get:

Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I want to set its current identity value to 15953711. So I ran this command:

DBCC CHECKIDENT (seq_audit, RESEED, 15953711)
GO

And this is the result I get:

Checking identity information: current identity value 'NULL', current column value '15953711'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I thought it worked so I again check its current identity by running this command:

DBCC CHECKIDENT (seq_audit, NORESEED) 
GO

But I was not expected the result I get:

Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So somehow the setting of current identity value is not working. Why? What am I doing wrong here?

Solution 1

This is caused by the fact that your table is empty. Try adding a single record and then everything will work. I have tried this and can confirm that it works.

Also, if you use SQL Server Management studio you can use the design feature to change the seed values. and manually add and delete records.

Solution 2

https://msdn.microsoft.com/es-es/library/ms176057(v=sql.120).aspx

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

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.

Also why you dont start the seed on the create table IDENTITY?

Sql Fiddle Demo

CREATE TABLE [dbo].[seq_audit](
    [id] [bigint] IDENTITY(15953711,1) NOT NULL,
    [value] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY];

Comments

[ChumboChappati]

Your suggestion You create the table, you can INSERT to test and then do TRUNCATE TABLE. and RESEED did not work. INSERT set the identity to 1, TRUNCATE set the identity back to NULL, after RESEED to 15953711, I checked and identity is still NULL.

[ChumboChappati]

Thanks. It worked this way: INSERT 1 row, RESEED to 15953711, DELETE 1 row. Now the current identity value is 15953711 and there are no rows.

[ChumboChappati]

It worked this way: INSERT 1 row, RESEED to 15953711, DELETE 1 row. Now the current identity value is 15953711 and there are no rows.

[Juan Carlos Oropeza]

Check my edit. Set the seed on the create table instead.