logoSign upLog in
Sandip Patel

Sandip Patel

To SQL Server05/09/2015

I have just described a difference about identity value is inserted after delete and truncate the records on table. I have faced the first scenario of delete the records on table. There is following a Query batch. create table #tab (id int identity(1,1)) insert #tab default values--1 insert #tab default values--2 insert #tab default values--3 insert #tab default values--4 insert #tab default values--5 insert #tab default values--6 select id as initial_data from #tab --- Result is initial_data 1 2 3 4 5 6 delete from #tab --deleting all records insert #tab default values select id as data_after_delete_identity_value from #tab -- Result is data_after_delete_identity_value 7 The Above scenario, After delete the all records from table, one default value is inserted and it's identity value is 7 means that When User delete the records from table then table deleted LOG keeps in DB. So that after delete one record is inserted, an identity value is incremented based on log record. Whereas another scenario is, I have truncate the table, the result is deffer shown using following Queries. truncate table #tab --TRUNCATING the table insert #tab default values select id as data_after_truncate_identity_value from #tab -- Result is data_after_truncate_identity_value 1 Above SQL-Statement, The result is shown 1 after truncate the table, That means When Use can truncate the table records, it does not keep LOG record in DB. So that after truncate table one record is inserted, an identity value is started default value (like 1).

Sandip Patel
Relevant