Database Fundamentals #16: Removing Data With T-SQL

Deleting data from a table using T-SQL works quite a lot like the UPDATE statement.

How it Works

In the same way you supply the statement, DELETE, and then the table name. You’re not going to specify columns in any way because deleting data is all about removing a row. If you just wanted to remove the values in a column, you would use the UPDATE statement. Because of this, the only other thing you need for a DELETE statement is the WHERE clause. Just like with the UPDATE statement, if you don’t supply a WHERE clause, then the DELETE statement will remove all data in the table. Be very careful about using this statement. Make sure you’ve always got a WHERE clause.

This example would delete all the rows in the table. Don’t run it unless you’re prepared to re-enter all the data.

DELETE Personnel.Person;

While this statement can be used to delete all rows from a table, it’s actually not the best way to perform that action. Because of how SQL Server keeps track of operations against data through a process referred to as the Transaction Log, deleting all data from a table through the statement results in all of those rows being part of a transaction in the transaction log. This makes everything much more time consuming. Instead, the way you remove all data from a table is with the TRUNCATE statement. Again, don’t run this unless you’re prepared to re-enter all the data:

TRUNCATE TABLE Personnel.Person;

That will immediately remove all data from the table. It’s still what is called a logged operation, meaning it gets an entry in the transaction log, but it’s only the one entry, not one for each row. The statement is slightly different in structure since you have to specify that you’re truncating a table as well as supplying the name of the table you are truncating. But the end result is the same. This removes all data. It’s also another example of how the T-SQL language is so much more efficient than the GUI.

TRUNCATE does have some limitations. When there are foreign key constraints in place to enforce referential integrity (a topic we’ll cover in a later blog post), you can’t use the TRUNCATE statement. In that case you either have to remove the constraints in order to use TRUNCATE or go back to using the DELETE statement and deal with the performance impact.

WHERE Clause

Most of the time, you’re going to be deleting a specific row or set of rows. This means that you have to use the WHERE clause. To delete the PersonID where the value is equal to 6 you would use a statement like this:

DELETE Personnel.Person
WHERE PersonID = 6;

If you run this, it will remove the row that meets the criteria in the WHERE clause. It will do this immediately. You won’t get a friendly reminder that this is a permanent action. Instead, you get an immediate, and permanent, delete. It will show the number of rows affected just like with the UPDATE statement. Knowing how many rows you affected is always a good way to know if the statement did what you expected.

Up to now you’ve only ever used the equal operator with the WHERE clause. It’s actually much more powerful than that. Let’s assume you want to get rid of multiple rows in the table. Instead of issuing multiple DELETE commands, you can modify the WHERE clause so that it uses a greater than operation rather than an equality operation like this:

DELETE Personnel.Person
WHERE PersonID > 3;

This will remove all rows that meet the criteria defined in the WHERE clause.

Because the TRUNCATE command works on the table, not on rows, there is no WHERE clause applicable to it.

Conclusion

There are even more ways you can work with the WHERE clause. We’ll explore some of them in subsequent blog posts when we talk about how to retrieve data from the database. Just don’t forget the importance of the WHERE clause when it comes to operations that will modify or remove the data in your database. Getting that wrong with the DELETE operation could lead to all sorts of pain.