{"id":548,"date":"2018-05-27T17:59:44","date_gmt":"2018-05-27T10:59:44","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=548"},"modified":"2020-04-11T20:13:28","modified_gmt":"2020-04-11T13:13:28","slug":"sql-server-truncate-table","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-truncate-table\/","title":{"rendered":"SQL Server TRUNCATE TABLE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL<\/code> Server TRUNCATE TABLE<\/code> statement to remove all rows from a table faster and more efficiently.<\/p>\n\n\n\n

Introduction to SQL Server TRUNCATE TABLE statement #<\/a><\/h2>\n\n\n\n

Sometimes, you want to delete all rows from a table. In this case, you typically use the DELETE<\/a><\/code> statement without a WHERE<\/a><\/code> clause.<\/p>\n\n\n\n

The following example creates a new table<\/a> named customer_groups<\/code> and inserts some rows into the table<\/a>:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> sales.customer_groups (\n    group_id<\/span> INT<\/span> PRIMARY KEY<\/span> IDENTITY<\/span>,\n    group_name VARCHAR<\/span> (50<\/span>) NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span> sales.customer_groups (group_name)\nVALUES<\/span>\n    ('Intercompany'<\/span>),\n    ('Third Party'<\/span>),\n    ('One time'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To delete all rows from the customer_groups<\/code> table, you use the DELETE<\/code> statement as follows:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> sales.customer_groups;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Besides the DELETE FROM<\/code> statement, you can use the TRUNCATE TABLE<\/code> statement to delete all rows from a table.<\/p>\n\n\n\n
The following illustrates the syntax of the TRUNCATE TABLE<\/code> statement:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> [database_name.][schema_name.]table_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, first, you specify the name of the table from which you want to delete all rows. Second, the database name is the name of the database in which the table was created. The database name is optional. If you skip it, the statement will delete the table in the currently connected database.<\/p>\n\n\n\n
The following statements first insert some rows into the customer_groups<\/code> table and then delete all rows from it using the TRUNCATE TABLE<\/code> statement:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> sales.customer_groups (group_name)\nVALUES<\/span>\n    ('Intercompany'<\/span>),\n    ('Third Party'<\/span>),\n    ('One time'<\/span>);   \n\nTRUNCATE<\/span> TABLE<\/span> sales.customer_groups;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The TRUNCATE TABLE<\/code> is similar to the DELETE<\/code> statement without a WHERE<\/code> clause. However, the TRUNCATE<\/code> statement executes faster and uses a fewer system and transaction log resources.<\/p>\n\n\n\n
TRUNCATE TABLE vs. DELETE #<\/a><\/h2>\n\n\n\n
The TRUNCATE TABLE<\/code> has the following advantages over the DELETE<\/code> statement:<\/p>\n\n\n\n
1) Use less transaction log #<\/a><\/h3>\n\n\n\n
The DELETE<\/code> statement removes rows one at a time and inserts an entry in the transaction log for each removed row. On the other hand, the TRUNCATE TABLE<\/code> statement deletes the data by deallocating the data pages used to store the table data and inserts only the page deallocations in the transaction logs.<\/p>\n\n\n\n
2) Use fewer locks #<\/a><\/h3>\n\n\n\n
When the DELETE<\/code> statement is executed using a row lock, each row in the table is locked for removal. The TRUNCATE TABLE<\/code> locks the table and pages, not each row.<\/p>\n\n\n\n
3) Identity reset #<\/a><\/h3>\n\n\n\n
If the table to be truncated has an identity column<\/a>, the counter for that column is reset to the seed value when data is deleted by the TRUNCATE TABLE<\/code> statement but not the DELETE<\/code> statement.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the TRUNCATE TABLE<\/code> statement to delete all rows from a table faster and more efficiently.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t