Reset Identity column to new id

Problem:

I have an enum database table with name DataTypes

Id             Name

--------------------

1              short

2               Int

3               long

and in c-sharp Classes i have 

enum string DataTypes{short=1,Int=2,long=3}

 and we used these references in other tables and classes.

Now we got a scenario where we have to add more datatypes

Id               Name

-----------------------

1                short

2                Int

3                long

4                Chara

5                string

6                float

If we delete chara from table and try to add again chara on 4 it is not possible we will 

get 7 Id for new chara element.

But we have kind of scenario where we want it to add with 4 Id value.

Solution:

Here we have three approaches we can use whatever is suites best to us:

1. Use Truncate:

 First what we can do if we are allowed to Truncate tables than Truncate it.

        Truncate table tablename

 After truncating table id will get reset to 0 but it will work if we dont have child table.

 If we have child parent relationship then first we need to delete referenced record from 

 child table and then only we can truncate table.

2. Reset Identity Value:

 Second way is to reset it directly using following command: 

   DBCC CHECKIDENT('TableName', RESEED, [NewValue])
 

 If for new insert value we want Id [new_Id] then :

 [NewValue]=[new_Id]-1 

 for eg.:

   In above case we want new value to 4 then reset command will be: 

   -- for example
   DBCC CHECKIDENT('DataTypes', RESEED, 3)

3. Set Identity OFF(before Insert Statement) and ON (after Insert Statement):

  In this solution we do it in three phase 

• Set Identity OFF for Table
• Run Insert data script but we need to specify Value of Id field explicitily
• Set Identity ON for Table

 For e.g:  

             Set identity Off

              Insert into DataTypes (Id, Name)

                               values (4, 'Char')

              Insert into DataTypes (Id, Name)

                               values (5, 'String')

              Set Identity ON

This is my findings through R&D on Identity Column.

Happy Living...

Happy Cocepts...

Happy Programming..