Reseed identity

1

hello
i need query to reseed identity table begin number 1
i used this query
DBCC CHECKIDENT ('Person.AddressType', RESEED, 0)
but identity begin ID (((0)))

2

Not sure why it is misbehaving (if it indeed is). Try to run this query and see if you are seeing the behavior you are expecting. The row that has second column = 'After' should have id value = 1.

use tempdb
go
-- table with identity	property, seed 50, increment 2.
create table dbo.TBL(id int identity(0,1),x varchar(32));

insert into TBL values ('Before1'),('Before2'),('Before3');
select * from TBL

dbcc checkident('dbo.TBL',RESEED,0);

insert into TBL values ('After')
select * from TBL;

drop table TBL;
3

thank you JamesK
i used this quey but some table (if table is empty data ) when insert row ID = 0

4

After you reseed, see what the values are using

DBCC CHECKIDENT('YourTableName',NORESEED);

If you have never inserted any data into the table or if you have truncated the table, the current identity value will come back as NULL. In that case, you can insert a row into the table to see what the next value inserted is.

5

How RESEED works is one of the quirks of SQL Server.

If the table is empty, the value you specified is used for the next row inserted; if table is not empty, value is seed + 1.

IF NOT EXISTS(SELECT 1 FROM Person.AddressType)
    DBCC CHECKIDENT ('Person.AddressType', RESEED, 1)