SQL – Truncate and Delete

Introduction Hi to all, here I am with a new article; topic of this article is “Difference between Truncate and Delete”. Truncate an Delete both are used to delete data from the table. These both the command will only delete the data of the specified table, they cannot remove the whole table data along with its structure. Now it’s ok that both the SQL statements are used to delete only the data from the table but they both differ from each other in many aspects like syntax, performance, resources uses etc. So, first let’s take a look of both of these terms (Truncate and Delete),

Truncate Truncate command in SQL removes all rows from a table without logging the individual row deletion in the transaction log. Truncate statement having the sane functionality as the Delete command has that is it deletes the data from the table without modifying or deleting the structure of the table. You can’t use the Where Clause with this (Truncate) statement. Syntax: TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name Table_name : Is the name of the table to truncate or from which all rows are removed. Simple it looks like below querry. TRUNCATE TABLE authors The above command will delete all data from the table author.

Delete Delete command in SQL also removes all rows from a table with logging the individual row deletion in the transaction log. You can use the Where Clause with this (Delete) statement. In case of delete I am writing just the simple syntax for more detailed explanation of delete syntax visit this link: DELETE (Transact-SQL). Syntax: DELETE FROM TABLE_NAME[ { database_name.[ schema_name ]. | schema_name . } ] table_name Table_name : Is the name of the table to truncate or from which all rows are removed. Simple it looks like below querry. DELETE FROM authors The above command will delete all data from the table author. In case of delete statements you can limit your delete query using where clause to delete, only particular records that fulfills the condition of where clause will be deleted not the all records. It looks like below querry with where clause. DELETE FROM authors Where AuthorId IN (1,2,3)

Differences between Truncate and Delete Now above you have seen the delete and truncate statements. Both the statements are functionally same, but still there are many differences exists between both of these, those differences with explanation are as follows:

Delete and Truncate removes the data not the structure Both commands removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

Conditional based deletion of data So, the conditional based deletion means, let’s suppose I am having a table authors and from this table I want to delete the authors that are living in Australia. So can we perform conditional based deletion with truncate and delete?? Answer is as follows:

Truncate – In case of truncate command we can’t perform the conditional based deletion because there is no WHERE clause present with this command. Delete – Delete command provides the functionality of conditional based deletion of data from the table using the WHERE clause.

Delete and Truncate both are logged operation Normally on most of the articles I read on internet I have seen that they says “delete is a logged operation and truncate is not a logged operation”, that means when we run the delete command it logs (records) the information about the deleted row and when we run the truncate command it doesn’t logs (records) any data. But this is not true, truncate is also a logged operation but in a different way and it uses fewer system and transaction log resources than delete. Because the truncate use minimum logging resources that is why it is faster than delete. So both delete and truncate are logged operation, how logging of both of these commands happen, let’s take a look:

DELETE is a logged operation on a per row basis. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So, in case if you are deleting huge number of records then it can cause your transaction log to grow very rapidly, meaning the deletion of huge number of records will use more server resources because it logs each and every row that is deleted and that is why your transaction log will grow very rapidly. Because the delete statement does recording of each deleted row that is why this is slow. From here some people asks that if this done each row recording than Why not Microsoft modify the delete statement that it will not record each deleted row??? So the answer is in most of the cases, when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover your database to the most recent state. TRUNCATE logs the deallocation of the data pages in which the data exists. TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows from the table. It won’t log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, “Books Online (BOL)” refers to TRUNCATE operations as “minimally logged” operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state. To be more specific lets take a look of an example, which will tell you that truncate is also a logged operation no matters that it is not fully logged like Delete but still it’s a logged operation. So take a look of following example. /*Create a dummy table in a non-production(dummy) database.*/ CREATE TABLE tranTest ( Id int Identity(1,1), Name Varchar(100) ) /*Now insert the records in the tranTest table.*/ INSERT INTO tranTest(Name) VALUES(’prashant’) INSERT INTO tranTest(Name) VALUES(’prateek’) INSERT INTO tranTest(Name) VALUES(’praveen’) INSERT INTO tranTest(Name) VALUES(’prakash’) INSERT INTO tranTest(Name) VALUES(’prabhat’) /*Then as you know delete is a looged operation that means in a transaction if we rollback the transaction after deleting the records from table, it will restore all deleted records.*/ BEGIN TRAN DELETE FROM tranTest SELECT * FROM tranTest ROLLBACK SELECT * FROM tranTest /*Now delte all records from teh table.*/ DELETE FROM tranTest /*And Insert new fresh records in teh table.*/ INSERT INTO tranTest(Name) VALUES(’prashant’) INSERT INTO tranTest(Name) VALUES(’prateek’) INSERT INTO tranTest(Name) VALUES(’praveen’) INSERT INTO tranTest(Name) VALUES(’prakash’) INSERT INTO tranTest(Name) VALUES(’prabhat’) /*Then as you now you know that Truncate is also a logged opertion so it must recstore all records that is delete from the tranTest table in the below tansaction*/ BEGIN TRAN TRUNCATE TABLE tranTest SELECT * FROM tranTest ROLLBACK SELECT * FROM tranTest /*So when we run the above command then Truncate also restores all teh records, that means somewhere the logging of truncate also done, but with minimal resources. Here i think minimal resources means you can restore the Truncate staements for that particular session but if you close the connection then i think it will not able to restore your data. BUT Delete will be able to restore your data later also.*/ So now I think it’s enough discussion on this topic to prove that truncate is also a logged operation in SQL Server. Let’s move to other difference. Behavior of Delete and Truncate for identity columns Ok, now the case of identity columns. Both truncate and delete commands behaves differently against Identity columns. When we use truncate it will reset the counter used by an identity column for new rows to the seed value defined for the column. But in case of DELETE it will not reset the counter of your identity column rather it maintains the same counter for new rows. In both the cases, if no seed was defined the default value 1 is used. As Truncate resets the Identity column counter so, in case you want to retain the identity counter, use DELETE instead of TRUNCATE. Why these two behaves differently??? I don’t know But What I this is SQL providing you the two ways two work, meaning in case you want that all the data from the table will be deleted and the counter will restarts from 1 then in that case truncate can help you. Else if you want to delete all the records but don’t want to reset you counter then delete is there for you. Example for this is here: /*First if u already created the tranTest table then delte it from you database.*/ DROP Table tranTest /*Then Create a dummy table in a non-production(dummy) database with an Identity column*/ CREATE TABLE tranTest ( Id int Identity(1,1), Name Varchar(100) ) /*Now insert the records in the tranTest table.*/ INSERT INTO tranTest(Name) VALUES(’prashant’) INSERT INTO tranTest(Name) VALUES(’prateek’) INSERT INTO tranTest(Name) VALUES(’praveen’) INSERT INTO tranTest(Name) VALUES(’prakash’) INSERT INTO tranTest(Name) VALUES(’prabhat’) /*If you run the below querry you’ll see that the max value of the identity column is 5 caz we have inserted only five records in this table.*/ SELECT * FROM tranTest /*Now delete all the records from the table using the delete command.*/ DELETE FROM tranTest /*Now by running the above command your table is empty, so insert the new 5 records in the table to see that after using the delete command from where identity will tart, fom 1 or from 6.*/ INSERT INTO tranTest(Name) VALUES(’prashant’) INSERT INTO tranTest(Name) VALUES(’prateek’) INSERT INTO tranTest(Name) VALUES(’praveen’) INSERT INTO tranTest(Name) VALUES(’prakash’) INSERT INTO tranTest(Name) VALUES(’prabhat’) /*After running the below query u’ll see that your identity now starts from 6 because delete do not resets the counter of your identity column*/ SELECT * FROM tranTest /*Now drop your table again and create it again.*/ DROP TABLE tranTest /*Create a dummy table again in a non-production(dummy) database to see the effect of truncate command on identity columns*/ CREATE TABLE tranTest ( Id int Identity(1,1), Name Varchar(100) ) /*And Insert new fresh records in teh table.*/ INSERT INTO tranTest(Name) VALUES(’prashant’) INSERT INTO tranTest(Name) VALUES(’prateek’) INSERT INTO tranTest(Name) VALUES(’praveen’) INSERT INTO tranTest(Name) VALUES(’prakash’) INSERT INTO tranTest(Name) VALUES(’prabhat’) /*Now at this point the counter of this table’s identity column is 5 taht is the max value of id column*/ SELECT * FROM tranTest /*Then truncate the table.*/ TRUNCATE TABLE tranTest /*and insert new records*/ INSERT INTO tranTest(Name) VALUES(’prashant’) INSERT INTO tranTest(Name) VALUES(’prateek’) INSERT INTO tranTest(Name) VALUES(’praveen’) INSERT INTO tranTest(Name) VALUES(’prakash’) INSERT INTO tranTest(Name) VALUES(’prabhat’) /*Now u’llsee that after truncating the table the identity is reset to its seed value.*/ SELECT * FROM tranTest /*So this example explains the beahviour of both of these command for Identity columns.*/

TRUNCATE is a DDL command whereas DELETE is a DML command This is also a common difference you have read in many articles. That is TRUNCATE is a DDL (data definition language) operation and DELETE is a DML (data manipulation language) operation. Yes according to SQL server it’s true. But why it is so, why TRUNCATE is DDL and DELETE is DML? Let’s look at this; Actually when we run the TRUNCATE command it puts a “Schema modification (Sch-M)” lock on the table. Now, what is “schema modification (Sch-M)”? The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released. Now you’ll ask that it blocks any modification to the table whereas in case of TRUNCATE we are performing modifications because we are deleting the data? But deleting the data is the one side of coin only what we see but the internal working of truncate is different, because as you read above that it doesn’t remove the data rather it deallocates the data pages, because the TRUNCATE doesn’t perform any data modification in the table that is why the DELETE TRIGGER is not called. So due to this fact I think we are not modifying the data of the table, BUT as you know TRUNCATE resets the Identity counter of the column in the table, which means the TRUNCATE is modifying the table definition or structure which comes under DDL operations. And also when you are truncating a table in the mean time you can’t modify or add any data to the table. So, to become a DDL operation you have to fulfill some of the conditions written below:

Modifying a table structure or definition comes under DDL operations, and When you are modifying the table structure in the mean time you can’t access the table to do any data modification. Here the TRUNCATE is doing all the activities that proofs that TRUNCATE is a DDL operation.

Now we move to the DELETE command. In case of delete command I am not sure which lock is implemented, but as we know and you can read above that DELETE command deletes the row one by one, that is its modifying the data by deleting it from the table, because the DELETE perform data modification that is why the DELETE TRIGGER is called when we issue a DELETE command for a table. DELETE command does not modify the table structure in any manner, as like TRUNCATE modifies the identity column by resetting its value but the DELETE doesn’t do this. So, to become a DML operation you have to fulfill some of the conditions written below:

Modifying the table data. When you are modifying the table data in the mean time you can’t perform any table structure modification on the table. Here the DELET modifying the data of the table and also when delete statement is running you can’t modify the table structure. So we can say that DELETE is a DML operation.

Behavior of Truncate and Delete for Triggers Now Triggers, another most important topic in SQL Server, but right now I am not talking about triggers, I am talking about How both TRUNCATE and DELETE behaves differently for Triggers and Why is it so??? As you all know that triggers fire whenever any data modification happens in the table. In case of TRUNCATE and DELETE because they are deleting the data from the table so, the DELETE TRIGGER will fire if present and also the INSTEAD OF and AFTER triggers will be triggered if present for DELETE TRIGGER. INSERT and UPDATE will not be fired here.So let’s take both one by one:

Truncate – When we run truncate command to delete all rows of table then, as you have read above that it actually doesn’t remove any row rather it deallocates the data pages, So in case of Truncate command triggers will not be fired because here no modification takes place, we have just deallocated the data pages not deleted any row from table. As we know that TRUNCATE is a DDL command, and DDL commands doesn’t modify your data instead they modify your table structure and definition. Delete – In case of DELETE the DELETE trigger will be fired if present and also if the INSTEAD OF and AFTER triggers for DELETE TRIGGER is present then they also will be fired. As we know that delete command is a DML command and it deletes the data on row-by-row basis. So that means delete is modifying the data by deleting it from the table. As we know delete is a DML command and trigger will be fired whenever any DML operation on the table takes place. So the conclusion is in case of Truncate triggers will not be fired and in case of Delete they will be fired if present.

Where we can use these statements Where we can and where we can’t use these statements. There are some restrictions on the use of both of these statements; restrictions for both statements are as follows: For DELETE:

The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.

For TRUNCATE You cannot use TRUNCATE TABLE on tables that:

Are referenced by a FOREIGN KEY constraint. Participate in an indexed view. Are published by using transactional replication or merge replication. So, keep in mind these restrictions and then use the Delete or Truncate statements.

Permissions of performing TRUNCATE or DELETE operation For using both the statements you need some permission from the server, which decides that you can perform the Delete or Truncate action on the table or not. To truncate a table, you need at least ALTER permissions on the table (which is granted by default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles). To delete rows using the delete statement, you only need the DELETE permission.The above is the detailed explanation of the differences between both of these statements. But if you need a basic knowledge about the differences between both of these then those are as follows,

Differences between Truncate and Delete in brief

Both statements delete the data from the table not the structure of the table. Both are logged operations. You can use WHERE clause with DELETE but with TRUNCATE you can’t use the WHERE clause. TRUNCATE is faster than DELETE. A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired if you are using DELETE. TRUNCATE is a DDL command whereas DELETE is a DML command. To use TRUNCATE on a table you need at least ALTER permission on the table and for DELETE you need delete permission on the table. TRUNCATE will not work on tables, which are referenced by one or more FOREIGN KEY constraints. TRUNCATE uses the less transaction space than DELETE statement. Fewer locks are used when using TRUNCATE. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row. TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter. TRUNCATE cannot be used with Indexed views, but Delete can be used with indexed views. Now this article is completed. Please give your comments about this article. Reference : Prashant Pandey (TechAhead)

*******