There is always a new beginning when we get rid of the obsolete /unused /wrong data .
True to the saying ‘To err is human’ , we always come across some data correction in our tables .Its always quite frustrating to see loads of data getting piled up into our tables . Not only for the bulkiness of data but also certain unused data or some obsolete data makes it really difficult to query a table .
This is where the commands truncate and delete come into play. These two commands definitely help us remove wrong ,unused or old data just at once. Also as a beginner in sql server we all have that one common doubt circling our minds ‘What is the difference between Delete and Truncate?’ ,just because both do pretty same job . Although both have some significance difference .
The main difference between truncate and delete commands is that , truncate is a DDL command and delete is a DML command .Getting some glance about DDL and DML will help with further content.
DDL Commands :
DDL is the abbreviation of Data Definition Language .In simple words it is used to define the structure of the objects in database . A common example is that ,while constructing a house we just define where the bricks have to be placed and in what ways it has to be designed. In the same way we try to define the structure of the objects in database . Some DDL commands are CREATE ,ALTER ,DROP ,TRUNCATE.
DML Commands :
DML is the abbreviation of Data Manipulation Language . These commands are used to retrieve , store ,modify ,insert ,update, delete data etc in a database . Here for a common example , we can say like doing an interior design in an house. Now that my data building is ready now i can add furniture to it, change color of the paint,remove unwanted things in my space or modify a wall based on my need ,upgrade a furniture etc. So this simply means i can modify and do whatever with the data. Some DML commands are SELECT, INSERT, UPDATE, DELETE.
retrieval , storage ,modification ,insertion ,updation, deletion of data
DML commands
Differences between Truncate and Delete:
To show the difference between two i have created two tables called dbo.LessonTruncate and dbo.LessonDelete and inserted 3 values into each of them.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE LessonTruncate ( LessonId INT IDENTITY (1,1), LessonName VARCHAR(50), CreditScore INT ); CREATE TABLE LessonDelete ( LessonId INT IDENTITY (1,1), LessonName VARCHAR(50), CreditScore INT ); |
Syntax :
1 2 3 4 5 6 7 8 9 | -- The syntax for Truncate is as follows: TRUNCATE TABLE LessonTruncate; -- The syntax for Delete is as follows: DELETE LessonDelete; --(or) DELETE FROM LessonDelete; |
Identity Value reset property:
An important detail in truncate is that the table identity is reset to its initial value once the truncate is done and new rows are inserted into the table . But in delete it does not reset instead continues from the number it left before deleting.
Use of ‘Where’ clause:
One advantage of ‘delete’ is that , where clause can be used ,whereas in truncate it cannot be used.
Performance:
Truncate is more faster when compared to delete . This happens because truncate is not logged in the log file while delete is logged into it. The log file can be viewed in Object Explorer —>Management —-> SQL Server Logs and click on to a file for viewing . Below query can also be used to find the operation that has happened.
1 2 3 | SELECT * FROM sys.fn_dblog(NULL,NULL) |
The Rollback Myth:
It is always common to hear the saying as ‘Truncate cannot be rolled back’ , but that is a myth .Both truncate and delete can be rolled back .But truncate cannot be rolled back from logs since its not logged into the log file. Truncate can be rolled back in the same session.
