Click here to Skip to main content
14,302,209 members

SQL Truncate and Delete

Rate this:
3.00 (4 votes)
Please Sign up or sign in to vote.
3.00 (4 votes)
14 Aug 2019CPOL
Truncate and Delete in SQL

In SQL Server, there are a couple of ways in which you can delete rows from a table. You can use the TRUNCATE and DELETE command. Though the end result of both commands is the same, there are very important differences you should know about.

What's the Difference between Truncate and Delete?

The TRUNCATE command is like a DELETE command without the WHERE clause with much less of a safety net.

When to Use TRUNCATE

When you TRUNCATE a table, less information is logged. This means the TRUNCATE statement executes very fast; however, it does so at the expense of not logging each row deleted. This means that you need to be very careful when using the command (actually, be careful with DELETE as well!).

Though you are able to rollback a TRUNCATE command in SQL Server, you cannot do the same in Oracle.

The TRUNCATE command is simple yet extremely dangerous. Here is an example to remove all rows from the employee table:

TRUNCATE TABLE employee

If you mistakenly execute a TRUNCATE statement, it is much more difficult to recover, and you may lose data in the process. The TRUNCATE command does log the pages it removes, so it is possible to recover the pages using some advanced code.

Here are some reasons to use TRUNCATE:

  1. You want to “reset” a table to its empty state. All rows are removed, and identity key values reset to the initial defined values.
  2. You need to have a super quick way of clearing out table data. I can see this occurring when you need to repeatedly import test data or you have routines that use work tables or scratch tables to store information.
  3. You want to remove rows from a table without activating the table’s after delete trigger.

Keep in mind that TRUNCATE will lock the table, so obviously don’t use this command on a table being shared by many concurrent users.

Tech Tip: Run queries and monitor performance of your SQL server remotely using powerful virtual PCs from CloudDesktopOnline. You can also rent a server at an affordable price and dedicated migration support from Apps4Rent.

When to Use the DELETE Command

The DELETE command is used to remove records from a database. It is the most common way to do so. In its simplest form, you can remove all the rows from a database or you can add a WHERE clause to remove only those meeting the criteria.

When execute the DELETE command, the DBMS logs all removed rows. This means it is easier to recover from a mistake, than it would a mistaken TRUNCATE.

The command:

DELETE FROM employee

will remove all employees from the employee table; whereas,

DELETE FROM   employee
WHERE  firstName = ‘Kris’

deletes all employees whose first name is Kris.

I would pretty much recommend using a DELETE statement in all cases, except for those special circumstances that merit a TRUNCATE.

Here are some things that happen during a DELETE that don’t during the TRUNCATE:

  1. Any deletion triggers are executed on the affected table.
  2. You are allowed to DELETE records that have foreign key constraints defined. A TRUNCATE cannot be executed if these same constraints are in place.
  3. Record deletions don’t reset identity keys. This is important when you need to guarantee each row uses a key that has never been used before. Perhaps, this needs to happen for audit reasons.
  4. Depending on the locking you are using, row locks are placed on deleted rows. Unaffected rows remain unlocked.

Conclusion

I should point out that TRUNCATE is considered a DDL command; whereas, DELETE is DML. I think this distinction should help you further understand when to use either command and the implications for doing so.

In a nutshell, use DELETE to remove one or more rows from a table. Only in special situations, such as when you need to reset a table to its initial state should you consider TRUNCATE.

Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --
Technical Blog
Posted 14 Aug 2019

Stats

2.4K views