I have a Table called Person, this table is non-empty and it's filled with records ( more than 1000 rows )

How can I reset the identity seed of this table?

The query was executed, but the table still has the identity

use [MyDatabase]
DBCC CHECKIDENT ('dbo.Person', RESEED, 0)
GO

Expected result of the table after executing the previous query :

CodePudding user response：

The problem is your understanding; the code is very likely working exactly as it is supposed to, and as I demonstrated.. RESEED resets the value of the next IDENTITY generated, it doesn't change any of the existing values.

Take SQL similar to what I gave in the comments:

CREATE TABLE dbo.Person (ID int IDENTITY(1,1), AbligatoryColumn char(1));
GO

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT N1.N
    FROM N N1, N N2, N N3)
INSERT INTO dbo.Person (AbligatoryColumn)
SELECT 'a'
FROM Tally;
GO

SELECT TOP (5) *
FROM dbo.Person
ORDER BY ID ASC;

If you run this you get the follow results:

ID          AbligatoryColumn
----------- ----------------
1           a
2           a
3           a
4           a
5           a

Now, let's RESEED the table, and INSERT another row:

DBCC CHECKIDENT ('dbo.Person', RESEED, 0)
GO
INSERT INTO dbo.Person
DEFAULT VALUES;
GO
SELECT TOP (5) *
FROM dbo.Person
ORDER BY ID ASC;

This gives the following data set:

ID          AbligatoryColumn
----------- ----------------
1           a
1           NULL
2           a
3           a
4           a

Notice that there are 2 rows where ID has a value of 1. This is because the new row we inserted has used thenew seed, so the next value generated was 1 (as when you RESEED you are defining the last value used, not the next value to be).

Note that you can't UPDATE the value of an IDENTITY, so if we tried the following you would get an error:

UPDATE dbo.Person
SET ID = ID   1000
WHERE AbligatoryColumn = 'a';

Cannot update identity column 'ID'.

The real question why do you want to change the value? An IDENTITY is just an arbitrary value, it's value doesn't matter.

If you "must" (and I would suggest you don't need to) you would need to CREATE a new table, INSERT the data from your existing table into it (likely with IDENTITY_INSERT enabled) and then DROP your old table, and rename the new one. If you have any foreign key constraints pointing to your current table, you'll need to DROP all of these, and I really hope you have any data referencing the existing ID values, as you'll then need to update all the foreign key values before you recreate the foreign key constraints. As a result your new table would (albeit likely temporarily) need to have both the old and new PK values in separate columns.

So, in truth, leave it as it is; the value of an IDENTITY literally doesn't matter. It's an arbitrary value and it whether the first value starts at 1, 17, or -167 or if there are numbers "missing" is irrelevant to functionality of what IDENTITY is there to achieve; an always ascending value.