SQL Server Reseed identity seed to always start with 1 - practical code

2izufjch 于 2023-05-05 发布在其他

关注(0)|答案(3)|浏览(142)

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?

sql-server

来源：https://stackoverflow.com/questions/46274736/reseed-identity-seed-to-always-start-with-1-practical-code

3条答案

按热度按时间

gpnt7bae1#

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

赞(0）回复(0）举报 2023-05-05

qgelzfjb2#

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

赞(0）回复(0）举报 2023-05-05

kx5bkwkv3#

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

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server Reseed identity seed to always start with 1 - practical code

3条答案

相关问题

热门标签

最新问答