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?

did you try inserting one record to see if it comes up with the right value? — Jeremy, Nov 06 '15 at 15:35
@Jeremy My job is only to set a specific identity value on this table; I am not authorized to insert any record in this table; workplace requirement. — ChumboChappati, Nov 06 '15 at 15:39
yeah preparing this table but the application is supposed to insert rows in this table — ChumboChappati, Nov 06 '15 at 15:41
You create the table, you can `INSERT` to test and then do `TRUNCATE TABLE`. and `RESEED` — Juan Carlos Oropeza, Nov 06 '15 at 15:41
or give it to the person authorized to insert rows to test and ensure it works — Jeremy, Nov 06 '15 at 15:42
Thanks both. So basically Microsoft does not allow setting identity value unless 1 row is added? — ChumboChappati, Nov 06 '15 at 15:44
You can set the identity... and next insert will use it. Read the manual, and if you dont trust it, just try it. — Juan Carlos Oropeza, Nov 06 '15 at 15:48

score 6 · Accepted Answer · answered Nov 06 '15 at 15:37

6

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.

answered Nov 06 '15 at 15:37

Peter Smith

5,528
8
51
77

1

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 Nov 06 '15 at 16:25

Juan Carlos Oropeza · Answer 2 · 2015-11-06T16:41:05.317

1

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];

edited Nov 06 '15 at 16:41

answered Nov 06 '15 at 15:39

Juan Carlos Oropeza

47,252
12
78
118

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 Nov 06 '15 at 16:19
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 Nov 06 '15 at 16:25
Check my edit. Set the seed on the create table instead. – Juan Carlos Oropeza Nov 06 '15 at 16:41

score 0 · Answer 3 · edited Oct 09 '17 at 10:50

0

The first value you will insert, will have identity value as 15953711. You may start inserting.

edited Oct 09 '17 at 10:50

Martin Evans

45,791
17
81
97

answered Oct 09 '17 at 10:28

Murali Dhar Darshan

285
1
17

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

3 Answers3

Linked