Reseed Identitity column

• shubham.saxena67

  Default port

  Points: 1490

  More actions

  April 12, 2011 at 11:24 pm

  #145077

  First of all Hello to all my experts,

  I am having a table 'TB_Bill' in that i have taken an identity column (bill_no)..

  but for some reason i need to delete random row from this table..

  for eg:(bill_no is identity column)

  TB_bill record like this..

  bill_no Product_code Pro_desc

  1 P_034 Prod. description1

  2 P_023 Prod. description2

  3 P_025 Prod. description3

  4 P_028 Prod. description4

  and for some reason i need to delete row where bill_no=3

  now i want to set identity column new number to 3 that means when i insert new row in it..this would be

  store with new bill_no=3...

  so is there any way to do this..send some sql statement or any suggession..

  Thanks to all in advance ...

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 12, 2011 at 11:47 pm

  #1310802

  There is, but the problem is that it's a complete reseed, so it will insert 3 for the next row, then try to insert 4 and give you either a duplicate or a duplicate key error.

  Why do you want to reuse? Gaps in an identity sequence are normal.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ysaias Portes-QUI Group

  SSCrazy

  Points: 2127

  More actions

  April 14, 2011 at 2:59 pm

  #1311871

  If you do not want to allow gaps and you know what the gaps are, you can use the "SET IDENTITY_INSERT" statement to insert data into the identity column manually.

  Here is an excellent document about using identity insert

  http://msdn.microsoft.com/en-us/library/ms188059.aspx

  QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  April 14, 2011 at 3:07 pm

  #1311877

  your code would have to

  a) know which value was deleted

  b) use something like this:

  set identity_insert tb_bill on

  insert tb_bill (bill_no, Product_code, Pro_desc) select 3, 'ABC', 'Third product'

  set identity_insert tb_bill off

  If this is a rare operation, this is fine. If it's something you are using in a process where you delete items, this can be a hassle. As Gail mentioned, gaps are normal if you delete items.

  Is there a reason you want to reuse this value?