9

I have a SQL table with an identity set:

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

Something has happened to this table, resulting in odd behaviour. I need to find out what.

When an insert occurs:

INSERT MyTable(RecordName) 
VALUES('Test Bug')

SELECT SCOPE_IDENTITY() -- returns 0
SELECT * FROM MyTable   -- displays: 0, 'Test Bug'

This is a problem because code above this insert expects the first ID to be 1 - I can't figure out how with IDENTITY(1,1) this ends up as 0.

If (before executing the INSERT) I check the identity it returns null:

DBCC CHECKIDENT (MyTable, NORESEED)

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

I know several ways to fix this; what I need to know how the table got into this state in the first place?

The only way I know that CHECKIDENT returns null is if the table's just been created, but then IDENTITY(1,1) is honoured and the INSERT causes SCOPE_IDENTITY() to be 1.

Alternatively I can get 0 as the next ID if I force -1 as the current seed (DBCC CHECKIDENT (MyTable, RESEED, -1) or with SET IDENTITY_INSERT MyTable ON) but then the check reports that current -1 seed (rather than null), so that can't be what's happened.

How did the database get into a state where the column has IDENTITY(1,1), DBCC CHECKIDENT (MyTable, NORESEED) returns null, but the next INSERT causes SCOPE_IDENTITY() to be 0?

Keith
  • 150,284
  • 78
  • 298
  • 434
  • I assume there is a trigger on table `MyTable`. – Hamlet Hakobyan Mar 05 '14 at 14:04
  • @HamletHakobyan There are no triggers on that table. – Keith Mar 05 '14 at 14:05
  • Have you achieve the same result when you recreate the table? – Hamlet Hakobyan Mar 05 '14 at 14:11
  • @HamletHakobyan no, that's what I'm asking - I know the state the table's ended up in, I need to know how it got into that state. – Keith Mar 05 '14 at 14:26
  • 1
    If you care about the numerical values of identity columns, you're using them wrong. All you should know is a) that they uniquely identify each row, and b) that you can *store* them in numeric columns. – Damien_The_Unbeliever Mar 05 '14 at 14:35
  • 3
    @Damien_The_Unbeliever we have an optimisation in code where IDs must be `>=1` before the overhead of a DB call is made. This helped us avoid an N+1 issue in a large amount of legacy code. If this was in a code base that we were writing from scratch this would be easy to avoid. – Keith Mar 05 '14 at 14:55

1 Answers1

15

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
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • Cheers! That was the problem - someone had called `DBCC CHECKIDENT (MyTable, RESEED, 0)` when they should have called `DBCC CHECKIDENT (MyTable, RESEED)`. I had no idea that still reported null as the current ident if the table had never been populated. – Keith Mar 05 '14 at 14:48
  • @Keith Did you mean to say that they should have called `DBCC CHECKIDENT (MyTable, NORESEED)`? – Doug_Ivison Mar 05 '14 at 16:46