Wednesday, August 29, 2012

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..

No comments:

Post a Comment