Skip to main content

Difference between SQL Truncate & SQL Delete

SQL - Difference between Truncate & Delete

1. TRUNCATE is a DDL (Data Definition Language) command and DELETE is a DML (Data Manipulation Language) command.

2. You can use WHERE clause with DELETE but not with TRUNCATE .

3. You cannot rollback data in TRUNCATE but in DELETE it is possible to rollback data.

4. A trigger doesn't get fired in case of TRUNCATE whereas Triggers get fired in case of a DELETE command.

5. TRUNCATE is faster than DELETE. TRUNCATE is faster than DELETE due to the way TRUNCATE "removes" rows. 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.

6. TRUNCATE resets the Identity counter if there is any identity column present in the table where DELETE does not reset the identity counter.

7. You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

8. DELETE and TRUNCATE operations are both logged. DELETE is a logged operation on a per row basis and TRUNCATE command logs the deallocation of the data pages in which the data exists.
Labels:

Popular posts from this blog

Dot Net FrameWork

The .NET Framework has two main components: the 1)Common Language Runtime (CLR) and the 2).NET Framework class library. The .NET Framework provides a Runtime environment called the Common Language Runtime or (CLR) that handles the execution of the code and provides useful services for the implementation of the application. CLR: The Runtime can be considered an agent that manages code at execution time. Thus providing core services such as memory management, thread management, and remoting. Also incorporating strict type safety, security and robustness. The Common Language Runtime (CLR) is the virtual machine component of the .NET framework. All .NET programs execute under the supervision of the CLR, guaranteeing certain properties and behaviors in the areas of memory management, security, and exception handling. Class Library: The class library is a comprehensive collection of reusable types that you can use to develop traditional command-line, WinForm (graphical user interface) appli...
Read more

SQL Difference between Union & Union All

SQL Difference between Union & Union All The union and union all operators allow you to combine multiple data sets. The difference between the two is that union sorts the combined set and removes duplicates while union all does not. With union all, the number of rows in the final data set will always equal the sum of the number of rows in the sets being combined.[Learning SQL By Alan Beaulieu] When using the UNION command all selected columns need to be of the same data type. For example : X Y UNION UNION ALL A B A A A B B A B A - B - - - B - - - B - - - A Union all is faster than union, union's duplicate elimination requires a sorting operation, which takes time.
Read more

Accept only numbers in an textbox.

This javascript function is used to make an text-box to accept number alone. JavaScript: function isNumberKey(evt)                                          {                                             var charCode = (evt.which) ? evt.which : event.keyCode                                             if (charCode > 31 && (charCode < 48 || charCode > 57))                        ...
Read more