Mike Flynn Mike Flynn - 2 months ago 7
SQL Question

Reseed back 1 with existing data in SQL Server

I have a table with alot of missing identity ids missing. I would also like to start it back at 1 with.

DBCC CHECKIDENT('Customer', RESEED, 0)


However, there is existing data in the table, so will this effect anything?

Say I have ID 2,3,5 in the table with 1 and 4 gone. If I execute the SQL above, does that start the next new row in that table at 1, then the next at 4 without bothering 2,3,5 and so on?

Answer

RESEEDING to 0 on table with existing data will cause duplicates of existing ID or raise error when Unique Constraints is available. Please see POC for further clarification of the same TestData:

IF OBJECT_ID(N'Table1') > 0
    BEGIN
       DROP TABLE Table1
    END

CREATE TABLE Table1 (ID INT IDENTITY(1, 1),
                 Col1 VARCHAR(3))

INSERT INTO Table1
VALUES('1F'),('3A'),('2A'),('4G'),('5X')

SELECT * FROM Table1

DELETE FROM Table1
WHERE ID IN( 2, 4)

SELECT * FROM Table1

DBCC CHECKIDENT('Table1', RESEED, 0)

INSERT INTO Table1
VALUES ('12F'),('34A'),('23A'),('45G'),('56X')

SELECT *
FROM Table1

DELETE FROM Table1

DBCC CHECKIDENT('Table1', RESEED, 0)

ALTER TABLE Table1
ADD UNIQUE (Id)

INSERT INTO Table1
VALUES('1F'),('3A'),('2A'),('4G'),('5X')

SELECT * FROM Table1

DELETE FROM Table1
WHERE ID IN( 2, 4)

DBCC CHECKIDENT('Table1', RESEED, 0)

SELECT * FROM Table1

INSERT INTO Table1
VALUES ('12F'),('34A'),('23A'),('45G'),('56X')

IF OBJECT_ID(N'Table1') > 0
    BEGIN
       DROP TABLE Table1
    END
IF OBJECT_ID(N'Table1') > 0
    BEGIN
       DROP TABLE Table1
    END