{"id":37333,"date":"2024-07-31T10:16:30","date_gmt":"2024-07-31T00:16:30","guid":{"rendered":"https:\/\/database.guide\/?p=37333"},"modified":"2024-07-31T10:16:32","modified_gmt":"2024-07-31T00:16:32","slug":"sql-truncate-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-truncate-explained\/","title":{"rendered":"SQL Truncate Explained"},"content":{"rendered":"\n<p class=\"\">In SQL databases, the <code>TRUNCATE<\/code> statement can be used to quickly remove all data from a table. Unlike the <a href=\"https:\/\/database.guide\/sql-delete-for-beginners\/\" data-type=\"post\" data-id=\"11582\"><code>DELETE<\/code> statement<\/a>, <code>TRUNCATE<\/code> is typically faster and uses fewer system resources, especially for large tables. <\/p>\n\n\n\n<p class=\"\">In this article, we&#8217;ll explore the <code>TRUNCATE<\/code> statement, its usage, and provide some examples.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Understanding <code>TRUNCATE<\/code><\/h2>\n\n\n\n<p class=\"\"><code>TRUNCATE<\/code> is a Data Definition Language (<a href=\"https:\/\/database.guide\/what-is-ddl\/\" data-type=\"post\" data-id=\"29412\">DDL<\/a>) statement that removes all rows from a table. It&#8217;s similar to a <code>DELETE<\/code> statement without a <code>WHERE<\/code> clause, but with some key differences:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"\"><code>TRUNCATE<\/code> is generally faster than <code>DELETE<\/code>.<\/li>\n\n\n\n<li class=\"\"><code>TRUNCATE<\/code> resets identity\/auto increment columns (if any) to their seed value.<\/li>\n\n\n\n<li class=\"\"><code>TRUNCATE<\/code> doesn&#8217;t trigger <code>DELETE<\/code> triggers (although some <a href=\"https:\/\/database.guide\/what-is-an-rdbms\/\" data-type=\"post\" data-id=\"222\">RDBMS<\/a>s may support <code>TRUNCATE<\/code> triggers).<\/li>\n\n\n\n<li class=\"\"><code>TRUNCATE<\/code> is not logged individually (which could have implications if you need to roll back).<\/li>\n<\/ul>\n\n\n\n<p class=\"\">The <code>TRUNCATE<\/code> statement is also known as the <code>TRUNCATE TABLE<\/code> statement, given it&#8217;s purpose of clearing all data from a table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p class=\"\">The basic syntax for <code>TRUNCATE<\/code> goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>TRUNCATE TABLE table_name;<\/code><\/pre>\n\n\n\n<p class=\"\">Although some RDBMSs (such as PostgreSQL) allow multiple tables to be truncated at once:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>TRUNCATE TABLE table_name_1, table_name_2, ...;<\/code><\/pre>\n\n\n\n<p class=\"\">The exact syntax will depend on the RDMS in question (PostgreSQL accepts various arguments, for example), but that&#8217;s generally how the <code>TRUNCATE<\/code> statement works.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p class=\"\">Let&#8217;s create a table and populate it with data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Create a sample table\nCREATE TABLE magical_creatures (\n    id INT IDENTITY(1,1) PRIMARY KEY,\n    creature_name VARCHAR(50),\n    habitat VARCHAR(50),\n    magic_power VARCHAR(50),\n    age INT,\n    rarity_score DECIMAL(5, 2)\n);\n\n-- Insert sample data\nINSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)\nVALUES \n    ('Sparkle Unicorn', 'Rainbow Forest', 'Glitter Blast', 342, 8.7),\n    ('Grumpy Gnome', 'Mushroom Village', 'Sarcasm Spell', 127, 3.2),\n    ('Floating Jellyfish', 'Sky Ocean', 'Antigravity Bubbles', 56, 7.5),\n    ('Lava Salamander', 'Volcanic Caves', 'Magma Manipulation', 789, 9.1),\n    ('Whispering Willow', 'Enchanted Grove', 'Telepathic Leaves', 1203, 6.8),\n    ('Thunderbird', 'Storm Clouds', 'Lightning Strike', 501, 8.9),\n    ('Frost Phoenix', 'Glacial Peaks', 'Ice Resurrection', 277, 9.3),\n    ('Bubblegum Kraken', 'Candy Sea', 'Sticky Tentacles', 892, 7.7),\n    ('Shadow Cat', 'Twilight Realm', 'Invisibility Purr', 99, 8.2),\n    ('Melodic Mushroom', 'Harmonic Caverns', 'Spore Symphony', 34, 5.6);\n\n-- View the data\nSELECT * FROM magical_creatures;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">id  creature_name       habitat           magic_power          age   rarity_score<br>--  ------------------  ----------------  -------------------  ----  ------------<br>1   Sparkle Unicorn     Rainbow Forest    Glitter Blast        342   8.7         <br>2   Grumpy Gnome        Mushroom Village  Sarcasm Spell        127   3.2         <br>3   Floating Jellyfish  Sky Ocean         Antigravity Bubbles  56    7.5         <br>4   Lava Salamander     Volcanic Caves    Magma Manipulation   789   9.1         <br>5   Whispering Willow   Enchanted Grove   Telepathic Leaves    1203  6.8         <br>6   Thunderbird         Storm Clouds      Lightning Strike     501   8.9         <br>7   Frost Phoenix       Glacial Peaks     Ice Resurrection     277   9.3         <br>8   Bubblegum Kraken    Candy Sea         Sticky Tentacles     892   7.7         <br>9   Shadow Cat          Twilight Realm    Invisibility Purr    99    8.2         <br>10  Melodic Mushroom    Harmonic Caverns  Spore Symphony       34    5.6         <\/pre>\n\n\n\n<p class=\"\">The above code works in SQL Server. Other RDBMs may have a slightly different syntax for the <code>CREATE TABLE<\/code> statement. For example, in MySQL we might use an <code><a href=\"https:\/\/database.guide\/how-auto_increment-works-in-mysql\/\" data-type=\"post\" data-id=\"30846\">AUTO_INCREMENT<\/a><\/code> or a <code><a href=\"https:\/\/database.guide\/serial-vs-auto_increment-in-mysql\/\" data-type=\"post\" data-id=\"30832\">SERIAL<\/a><\/code> column instead of the <code>IDENTITY<\/code> column.<\/p>\n\n\n\n<p class=\"\">Anyway, now that we have our whimsical table with data, let&#8217;s use the <code>TRUNCATE<\/code> command against it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Truncate the magical_creatures table\nTRUNCATE TABLE magical_creatures;\n\n-- View the data after truncation\nSELECT * FROM magical_creatures;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">0 row(s) returned<\/pre>\n\n\n\n<p class=\"\">After running the <code>TRUNCATE<\/code> statement, we can see that all rows have been removed from the table. <\/p>\n\n\n\n<p class=\"\">It&#8217;s important to note that the table still exists, and its structure remains intact.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><code>TRUNCATE<\/code> vs. <code>DELETE<\/code><\/h2>\n\n\n\n<p class=\"\">To illustrate the difference between <code>TRUNCATE<\/code> and <code>DELETE<\/code>, let&#8217;s repopulate our table and compare the two commands:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Repopulate the table\nINSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)\nVALUES \n    ('Sparkle Unicorn', 'Rainbow Forest', 'Glitter Blast', 342, 8.7),\n    ('Grumpy Gnome', 'Mushroom Village', 'Sarcasm Spell', 127, 3.2),\n    ('Floating Jellyfish', 'Sky Ocean', 'Antigravity Bubbles', 56, 7.5);\n\n-- Use DELETE to remove all rows\nDELETE FROM magical_creatures;\n\n-- Insert a new creature\nINSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)\nVALUES ('Disco Dragon', 'Funky Cavern', 'Groovy Flames', 178, 7.9);\n\n-- View the data\nSELECT * FROM magical_creatures;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">id  creature_name  habitat       magic_power    age  rarity_score<br>--  -------------  ------------  -------------  ---  ------------<br>4   Disco Dragon   Funky Cavern  Groovy Flames  178  7.9         <\/pre>\n\n\n\n<p class=\"\">We can see that the new row has an <code>id<\/code> of <code>4<\/code>, as <code>DELETE<\/code> doesn&#8217;t reset the identity column.<\/p>\n\n\n\n<p class=\"\">Now, let&#8217;s try with <code>TRUNCATE<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Repopulate the table\nINSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)\nVALUES \n    ('Sparkle Unicorn', 'Rainbow Forest', 'Glitter Blast', 342, 8.7),\n    ('Grumpy Gnome', 'Mushroom Village', 'Sarcasm Spell', 127, 3.2),\n    ('Floating Jellyfish', 'Sky Ocean', 'Antigravity Bubbles', 56, 7.5);\n\n-- Use TRUNCATE to remove all rows\nTRUNCATE TABLE magical_creatures;\n\n-- Insert a new creature\nINSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)\nVALUES ('Pixie Dust Panda', 'Bamboo Dreamland', 'Wish Granting', 88, 8.5);\n\n-- View the data\nSELECT * FROM magical_creatures;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">id  creature_name     habitat           magic_power    age  rarity_score<br>--  ----------------  ----------------  -------------  ---  ------------<br>1   Pixie Dust Panda  Bamboo Dreamland  Wish Granting  88   8.5         <\/pre>\n\n\n\n<p class=\"\">This time, we can see that the new row has an <code>id<\/code> of <code>1<\/code>, as <code>TRUNCATE<\/code> resets the identity column.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">SQLite<\/h2>\n\n\n\n<p class=\"\">SQLite doesn&#8217;t have a <code>TRUNCATE<\/code> or <code>TRUNCATE TABLE<\/code> statement, but it does have a thing called &#8220;truncate optimization&#8221;. Truncate optimization works like this:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"\">A default build of SQLite, if a <code>DELETE<\/code> statement has no <code>WHERE<\/code> clause and operates on a table with no triggers, an optimization occurs that causes the <code>DELETE<\/code> to occur by dropping and recreating the table. Dropping and recreating a table is usually much faster than deleting the table content row by row.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"\">See the <a href=\"https:\/\/www.sqlite.org\/lang_delete.html\" target=\"_blank\" rel=\"noreferrer noopener\">SQLite documentation for the <code>DELETE<\/code> statement<\/a> for more information.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Considerations<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"\"><code>TRUNCATE<\/code> typically requires different permissions\/privileges to the <code>DELETE<\/code> statement. For example, in SQL Server it requires at least <code>ALTER<\/code> permissions on the table. MySQL requires <code>DROP<\/code> privileges, and PostgreSQL requires <code>TRUNCATE<\/code> privileges.<\/li>\n\n\n\n<li class=\"\">In some RDBMSs, <code>TRUNCATE<\/code> cannot be used with tables that are referenced by <a href=\"https:\/\/database.guide\/what-is-a-foreign-key\/\" data-type=\"post\" data-id=\"217\">foreign key constraints<\/a> (unless the foreign key is disabled or not enforced). In others (such as PostgreSQL), we can control whether or not to truncate related tables.<\/li>\n\n\n\n<li class=\"\"><code>TRUNCATE<\/code> may not be available for all database systems or table types (e.g., some <a href=\"https:\/\/database.guide\/what-is-a-view\/\" data-type=\"post\" data-id=\"315\">view<\/a>-based tables).<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In SQL databases, the TRUNCATE statement can be used to quickly remove all data from a table. Unlike the DELETE statement, TRUNCATE is typically faster and uses fewer system resources, especially for large tables. In this article, we&#8217;ll explore the TRUNCATE statement, its usage, and provide some examples.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[20],"class_list":["post-37333","post","type-post","status-publish","format-standard","hentry","category-sql","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37333","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=37333"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37333\/revisions"}],"predecessor-version":[{"id":37382,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37333\/revisions\/37382"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=37333"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=37333"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=37333"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}