How to reset IDENTITY column values in SQL Server

A story of a massive reseed!

I recently needed to reseed to 1 the values of all the identity columns of the empty tables in a database.

Breaking down the request into smaller tasks, the following two were identified:

• Identify tables with no records in the database
• Reseed the IDENTITY column using a T-SQL script that can generate the T-SQL statements for reseeding the IDENTITY

To identify tables with no rows, the spaceused CTE developed in the article What are the biggest tables in your SQL database? can be used. The spaceused CTE has been expanded and has become a multiple CTE containing the zerospaceused query. The outer query can generate the DBCC CHECKIDENT commands with the RESEED option.

The EmptyDB sample database is used to test the script. EmptyDB contains two tables: dbo.Persons and dbo.Addresses, these tables are now empty they weren’t empty in the past.

The following T-SQL code generates the EmptyDB sample database.

USE [master];
GO

-- Drop Database
IF (DB_ID('EmptyDB') IS NOT NULL)
BEGIN
  ALTER DATABASE [EmptyDB]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

  DROP DATABASE [EmptyDB];
END;
GO

USE [master];
GO

CREATE DATABASE [EmptyDB]
 ON  PRIMARY 
 (
   NAME = N'EmptyDB'
   ,FILENAME = N'C:\SQL\DBs\EmptyDB.mdf'
   ,SIZE = 8192KB
   ,FILEGROWTH = 65536KB
 )
 LOG ON 
 (
   NAME = N'EmptyDB_log'
   ,FILENAME = N'C:\SQL\DBs\EmptyDB_log.ldf'
   ,SIZE = 8192KB
   ,FILEGROWTH = 65536KB
  )
 WITH LEDGER = OFF
GO

USE [EmptyDB];

CREATE TABLE dbo.Persons
(
  ID INTEGER IDENTITY(1, 1) NOT NULL
  ,FirstName NVARCHAR(64) NOT NULL
  ,LastName NVARCHAR(64) NOT NULL
);
GO

CREATE TABLE dbo.Addresses
(
  ID INTEGER IDENTITY(1, 1) NOT NULL
  ,Address1 NVARCHAR(128) NOT NULL
  ,Address2 NVARCHAR(128) NOT NULL
);
GO

INSERT INTO dbo.Persons (FirstName, LastName) VALUES ('Rob', 'Walters'), ('Gail', 'Erickson');
GO
DELETE FROM dbo.Persons;
GO
INSERT INTO dbo.Addresses (Address1, Address2) VALUES ('1970 Napa Ct.', '6387 Scenic Avenue');
GO
DELETE FROM dbo.Addresses;
GO

The following T-SQL code sets up DBCC CHECKIDENT commands in the @cmd local variable. If we run it when connected to EmptyDB, it will generate T-SQL commands to reseed IDENTITY of dbo.Persons and dbo.Addresses tables.

USE [EmptyDB];
GO

DECLARE
  @cmd NVARCHAR(MAX) = '';

WITH spaceused AS
(
  SELECT
    sys.dm_db_partition_stats.object_id
    ,reservedpages = SUM(reserved_page_count)
    ,it_reservedpages = SUM(ISNULL(its.it_reserved_page_count, 0))
    ,usedpages = SUM(used_page_count)
    ,it_usedpages = SUM(ISNULL(its.it_used_page_count, 0))
    ,pages = SUM(CASE
                   WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                   ELSE lob_used_page_count + row_overflow_used_page_count
                 END
                )
    ,row_Count = SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
  FROM
    sys.dm_db_partition_stats
  JOIN
    sys.objects ON sys.objects.object_id=sys.dm_db_partition_stats.object_id
  OUTER APPLY
    (SELECT
       reserved_page_count AS it_reserved_page_count
       ,used_page_count AS it_used_page_count
     FROM
       sys.internal_tables AS it
     WHERE
     it.parent_id = object_id
     AND it.internal_type IN (202, 204, 211, 212, 213, 214, 215, 216)
     AND object_id = it.object_id
  ) AS its
  WHERE
    sys.objects.type IN ('U', 'V')
  GROUP BY
    sys.dm_db_partition_stats.object_id
), zerospaceused
AS
(
SELECT
  table_name = OBJECT_NAME ([object_id])
  ,table_schema = OBJECT_SCHEMA_NAME ([object_id])
  ,rows = convert (char(11), row_Count)
  ,reserved = LTRIM (STR (reservedpages * 8, 15, 0) + ' KB')
  ,it_reserved = LTRIM (STR (it_reservedpages * 8, 15, 0) + ' KB')
  ,tot_reserved = LTRIM (STR ( (reservedpages + it_reservedpages) * 8, 15, 0) + ' KB')
  ,data = LTRIM (STR (pages * 8, 15, 0) + ' KB')
  ,data_MB = LTRIM (STR ((pages * 8) / 1000.0, 15, 0) + ' MB')
  ,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,it_index_size = LTRIM (STR ((CASE WHEN it_usedpages > pages THEN (it_usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,tot_index_size = LTRIM (STR ((CASE WHEN (usedpages + it_usedpages) > pages THEN ((usedpages + it_usedpages) - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,unused = LTRIM (STR ((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
FROM
  spaceused
WHERE
  row_Count = 0
)
SELECT 
  @cmd = STRING_AGG('DBCC CHECKIDENT (''' + table_schema + '.' + table_name + ''', RESEED, 1);', CHAR(13)) WITHIN GROUP (ORDER BY table_name)
FROM
  zerospaceused;

PRINT(@cmd);
GO

The generated output is the following…

DBCC CHECKIDENT ('dbo.Persons', RESEED, 1);
DBCC CHECKIDENT ('dbo.Addresses', RESEED, 1);

The action performed on the contents of the local variable @cmd is the display commands. PRINT(@cmd) displays the commands, EXEC(@cmd) applies them.

During script development I faced a strange behaviour on string concatenation in SQL Server 2022. Using old style string concatenation my output was always 1 row, an ORDER BY clause was involved in the outer query.

The following CTE outer query uses the old style string concatenation.

SELECT 
  @cmd = @cmd + 'DBCC CHECKIDENT (''' + table_schema + '.' + table_name + ''', RESEED, 1);' + CHAR(13) + CHAR(10)
FROM
  zerospaceused
ORDER BY
  table_name;

After some investigation I found the reason: for string concatenation, instead of the old style way, consider the STRING_AGG function, introduced in SQL Server 2017, for scenarios where ordered string concatenation is desired. Using ORDER BY in attempt to order concatenation causes list to be incomplete. You can find more details here SELECT @local_variable.

Enjoy!