In this step by step SQL Tutorial I will show you how to remove rows from a table using TRUNCATE TABLE. For the purpose of this tutorial I will use SQL Server 2012.
You might find this tutorial if:
- You are new to SQL and TRUNCATE TABLE statements
- You would like to see an example of TRUNCATE TABLE statement and some tips
- You are preparing for Microsoft Certification and TRUNCATE is part of the requirements
Prior knowledge: It might be easier for you to follow this tutorial if you have knowledge of DELETE FROM statement which gives you the same results. So why I would use TRUNCATE instead of DELETE FROM statement? You can find out in this tutorial.
We can use DELETE or TRUNCATE and the main result is the same; it simply removes rows from a table. The difference between them is that TRUNCATE TABLE removes rows without logging data and that results is very fast removal of the rows, which is very noticeable on big tables.
NOTE: Not logging data means you will not be able to recover data to certain point in time. This is important for production environments where disaster recovery needs to be in place.
TRUNCATE is fast but has limitation.
- First of all you need special right to use it (write permissions are not sufficient, TRUNCATE is part of DDL so you need ALTER permissions).
- You cannot delete part of the table. (DELETE FROM can) so all rows or nothing.
- If you have Foreign Key Constraint that refer to your table than you won't be able to use TRUNCATE TABLE.
To better understand our examples let's create a task that is close to real-life. In our story imagine you are a developer
that has been ask to clean big table before loading new data from a spreadsheet. In the following example I will show you how you could solve your task.
For this example I created new table called MyTable. In Object Explorer I go to AdventureWorksDW2012 and from Tables folder I find this one from which I want to remove all records. In this case its "MyTable"
My table contains only 6 records so there won't be much different between delete and truncate from performance perspective but if we had 6 million rows than we would see massive difference.
I will run TRUNCATE TABLE dbo.MyTable to remove all records from my table.
NOTE: Ensure you have selected correct database from database drop down box on the toolbar.
I can see that my code was run successfully.
I check if my table is empty and I can see that there are no records.
I hope this article gave you good introduction to TRUNCATE TABLE and we hope to write more articles related to common questions when using TRUNCATE TABLE soon.