Microsoft – SQLServer – Column Types – Identity – Reseting

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:

  1. Replace the “delete from ” with a “Truncate table”.  In our example above, “delete from dbo.supportDepartment” will be replaced with “Truncate table dbo.supportDepartment”
  2. 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);

References:

  1. DBCC CheckIdent
    http://msdn.microsoft.com/en-us/library/ms176057.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s