Delete Vs Truncate in SQL Server

We will see in this post How delete and truncate will work with temp table.

We have use many times truncate and delete command with Database Table and it works perfectly. It will allow truncating the table if that table doesn't have any records or if it has records then that table must not have any reference with other table.

If the table has reference with other table then we can not truncate table without deleting or truncating the reference table records. But we can delete the records from the reference table.

Now here we see how the truncate and delete command works with the Temp Table and see the difference between them.

We will see with the below examples.

Truncate:

Create one temp table with an identity field like below:

CREATE TABLE #Test

(

      ID INT IDENTITY(1,1),

      Name VARCHAR(10)

)

Insert some recors into #Test Table

INSERT INTO #Test

VALUES('PP'),('AA')

See the output using:

SELECT * FROM #Test

Here you can see there two records are inserted with identity value. Now we are truncating the temp table and inserting again these records and sees what will happen.

It has truncated the table and also reset the identity value to its original seed.

Delete:

Now we delete the records from temp table instead truncate and then again insert the records into temp table and see what will happen.

Let see:

We can see in above result that after deleting the records it has next identity value in ID field and it will insert the next increment value into the ID field.

Remove the table after execution using this:

DROP TABLE #Test

So here we conclude that the Truncate command will delete the records from table and also reset the identity value if any field has an identity set and reset it to the original seed value. While Delete command just deletes the record from table and not resetting any value for identity column.