Microsoft – SQLServer – Column Types – Identity – Reset
MS provides the Identity column has a way to lazily reap auto-generated numeric numbers.
They work quite well, the system automatically generates the number and you go your merry away. Who does not like or want that.
Having said that, there are a couple of quirks.
For instance, let us paint a picture:
use [tempdb]; if object_id('dbo.supportDepartment') is null ( create table dbo.supportDepartment ( [id] int not null identity(1,1) , [Name] sysname not null , [addedBy] sysname not null default SYSTEM_USER , [addedOn] datetime not null default getdate() ) alter table dbo.supportDepartment add constraint PK_SupportDepartment primary key ([id]); ) delete from dbo.supportDepartment; insert into dbo.supportDepartment([name]) values('HR'); insert into dbo.supportDepartment([name]) values('Economics'); insert into dbo.supportDepartment([name]) values('Janitorial'); insert into dbo.supportDepartment([name]) values('Athletics');
So if you have this kind of situation and you run this a few times, you might notice
that the id column does not always restart from 1.
To force a restart consider:
- Replace the “delete from ” with a “Truncate table”. In our example above, “delete from dbo.supportDepartment” will be replaced with “Truncate table dbo.supportDepartment”
- Use “dbcc checkident” and force a reseed
Syntax: dbcc checkident(<table-name>, reseed, 0): Sample (does not work): dbcc checkident('dbo.supportDepartment', reseed); Sample (works): dbcc checkident('dbo.supportDepartment', reseed, 0);
- DBCC CheckIdent