Wednesday, May 23, 2012

How to resets the IDENTITY back to the SEED without using truncate table command?

TRUNCATE TABLE : TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions

DELETE TABLE : DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary

Note:When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:
  • DELETE from "table_name"
  • DBCC CHECKIDENT("table_name", RESEED, "reseed_value")
eg: DBCC CHECKIDENT("Table1",RESEED,0)

No comments:

Loading...