Monday, March 26, 2012

Truncate Table SQL Server

I was asked in an interview not long ago what Truncate did in SQL Server.  I had remembered seeing a Truncate statement in the code of the project that I am currently working on, but couldn't remember what we used it for.  I came home and read up on it.

The statement is Truncate Table [table name].

It appears to be the equivalent to the DELETE statement without a where clause.  Basically it dumps all your records in the table.  This is handy for temp tables that you might be using and reusing for heavy lifting.

Truncate Table is faster than Delete.  It is likely that truncate doesn't pick each row, but just dumps them all.  Perhaps moving a record pointer to the top of the table and letting all the other memory be reclaimed.  It also doesn't appear to do all the logging that Delete does.  It you need logging you should stick with Delete and take the performance hit.

In many ways the end result of Truncate Table is the same as executing a Drop Table and then a Create Table with the exact same table layout.

There seem to be limitations on the Truncate command that it can't be used if the table has a foreign key constraint.  The Delete statement does not have that limitation.

No comments: