Truncation Versus Deletion

Richie Lee contrasts two methods of getting rid of data:

I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts:

  1. The TRUNCATE TABLE statement is a DDL operation, whilst DELETE is a DML operation.

  2. TRUNCATE Table is useful for emptying temporary tables, but leaving the structure for more data. To remove the table definition in addition to its data, use the DROP TABLE statement.

Read on for more details and a couple scripts to test out Richie’s statements.

Related Posts

Default Schemas In SQL Server

Daniel Hutmacher looks at specifying default schemas on a database: If your user is a database owner, (i.e. is a member of the db_owner group or has CONTROL permissions on the database) the default schema will always be dbo. This is something you can’t change. So if your legacy application needs quasi-administrative privileges in the database, you can’t make it […]

Read More

Window Function Basics

Doug Kline has a new series on window functions.  First, he looks at differences between RANK, DENSE_RANK, and ROW_NUMBER: — Quick! What’s the difference between RANK, DENSE_RANK, and ROW_NUMBER? — in short, they are only different when there are ties… — here’s a table that will help show the difference — between the ranking functions […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930