HomeProgramSQL Server

The difference between Truncate and Delete in Microsoft SQL Server

In the following article, we will help you distinguish some basic differences between two DELETE syntax and TRUNCATE TABLE in Microsoft SQL Server application. Basically, both of these statements help us to remove the data, but in essence it is not so.

TipsMake.com - In the following article, we will help you distinguish some basic differences between the DELETE and TRUNCATE TABLE syntax in Microsoft SQL Server application. Basically, both of these statements help us to remove the data, but in essence it is not so.

DELETE statement

This command will help us delete the records - Record from the database in rows - Row . When such a record is deleted with DELETE , all internal components are recorded in the Transaction Log section, the binding parts are checked, and any redundant components are completely deleted. In addition, the logs if accidentally deleted with this DELETE command can still be recovered. While DELETE is often used to delete table data from tables more often when applied in certain data sections, besides, this statement is often used for single data tables, or in the case of want delete data from a certain table when needed in conjunction with many other relational tables. On the other hand, delegating DELETE functions on the table is only applied on different user accounts without having to assign ownership on that account. And the DELETE statement does not change the automatic increase or decrease of the number of columns - Column in the table containing that data.

General syntax:

 [WITH [, . n]]
 DELETE 
 [TOP (expression) [PERCENT]] 
 [FROM] 
 {table_name [WITH ([. n])] 
 | | view_name view_name 
 | | rowset_function_limited rowset_function_limited 
 | | table_valued_function table_valued_function 
 } } 
 [  ] 
 [FROM [, . n]] 
 [WHERE { 
 | {[CURRENT OF 
 {{[GLOBAL] cursor_name} 
 | cursor_variable_name 
 } 
 ] 
 } 
 } 
 ] 
 [OPTION ([, . n])] 
 [; ] 

 :: = 
 { 
 [server_name.database_name.schema_name. 
 | database_name. [schema_name]. 
 | schema_name. 
 ] 
 table_or_view_name 
 } 
 TRUNCATE statement: 
 General syntax: 
 TRUNCATE TABLE 
 [{database_name. [schema_name]. | | schema_name . schema_name. } ] }] 
 table_name 
 [; ] 
 This TRUNCATE command will redistribute the page containing the data in the table, and all of that data will be erased completely after execution. In terms of speed, TRUNCATE is much faster than DELETE based on the amount of information stored in the Transaction Log section, and also so if certain records are accidentally deleted, it will not be recoverable. . In essence, TRUNCATE is a Data Definition Language operator - DDL, which also means that we need to have at least ALTER TABLE or higher permissions to perform. But the TRUNCATE TABLE decentralization does not exist. If a data table after executing TRUNCATE has the function of increasing the number of columns, it will automatically be rearranged according to the original definition. In addition, there are some limitations with the TRUNCATE syntax, and cannot be applied on specific tables like the situation below: 
 - When the data table is referenced by Index View. 
 - External links are shortened. 
 - That data sheet is used for copying. 
 - The table belongs to the database being logged. 
 When to use 
 Depending on the specific situation, please use the DELETE or TRUNCATE command accordingly. In it, DELETE syntax is used quite widely and popularly because it allows users to clearly identify which record to delete, combined with JOINS command and some other parameters. Blocking is possible when we use DELETE to delete a large amount of data, so users need to be extremely careful when manipulating. On the other hand, the TRUNCATE command will help the administrator delete an entire data table simply and quickly. 
 Good luck!
5 ★ | 1 Vote
delete sql serverdatabasesql servermssql

You should read it

Maybe you are interested

SQL Server