0

This issue is well described and it was explained why it happens (i.e. StackOverflow threads 1, 2.3), but what about practical reliable solution? I have number of scripts, which have to output tables (by external specification) with row IDs starting always from 1, regardless the table status (new or just with rows deleted).

The problem is following - If I run:

DBCC CHECKIDENT ('TableName', RESEED, 0);
  • on table which had rows (and were deleted), then ID will start with 1. By the way, I am using GO statments after the command. This is good.
  • but if I run this same command on newly created table, the first row will have ID=0, even if the IDENTITY(1,1) is set (in all cases listed here).

As was explained, this behavior is correct according to SQL Server specification. To avoid it, someone suggested to run just:

DBCC CHECKIDENT ('TableName', RESEED);

The first identity will be 1 on newly created table (at least in my envrionment settings it worked), but for tables deleted which had data which were deleted, it will hold the last seed (could be i.e. 123456).

I tried also:

DBCC CHECKIDENT ('TableName', RESEED, 1);

the first ID will be then 2.

So what is the practical way to ALWAYS get ID=1 for the first inserted row? In the attached links, someone suggested to insert a dummy row and delete it, then use "0" reseed and finally start inserting rows. Is this the correct approach?

Oak_3260548
  • 1,882
  • 3
  • 23
  • 41
  • Looks like you know your options then. I'd say go for inserting and deleting a dummy row and then re-seeding, as it looks like an easy fix. – Magisch Sep 18 '17 at 08:29
  • Provide any query how you delete records. – Stanislovas Kalašnikovas Sep 18 '17 at 08:30
  • 1
    If you care about the numerical values of an identity column, you're probably using it wrong. Treat them as opaque blobs that *happen* to fit in numeric columns and you're alright. Expecting certain values, sequential assignment, lack of gaps, etc and you're probably going to have problems – Damien_The_Unbeliever Sep 18 '17 at 08:33
  • @Damien_The_Unbeliever you're probably right. My initial design was ignorant to that, but a 3rd party partner now involved in the project insists on such row order due to the way they use entity framework... – Oak_3260548 Sep 18 '17 at 08:49

3 Answers3

0

You could check @@ROWCOUNT after deleting records to check if records were deleted.

If @@ROWCOUNT is higher than 0 then you use DBCC CHECKIDENT ('TableName', RESEED, 0); otherwise use DBCC CHECKIDENT ('TableName', RESEED, 1);

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • The @@rowcount make sense, I guess, but DBCC CHECKIDENT ('TableName', RESEED, 1) would result in first row having ID = 2, isn't it? – Oak_3260548 Sep 18 '17 at 08:43
0

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
Magisch
  • 7,312
  • 9
  • 36
  • 52
0
DECLARE @last_value INT;
  SELECT @last_value = CONVERT(INT, last_value)
  FROM sys.identity_columns
  WHERE object_id = OBJECT_ID('${table}');

  IF @last_value IS NOT NULL
  BEGIN
      -- If rows have been inserted to the table since it was created,
      -- or all rows have been removed by using the DELETE statement,
      DBCC CHECKIDENT ('${table}', RESEED, 0);
  END
  ELSE
  BEGIN
      -- 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,
      DBCC CHECKIDENT ('${table}', RESEED, 1);
  END
Sam Congleton
  • 1