Pages

▼

Friday, 7 October 2016

Delete Vs Truncate

DELETE
Removes one or more rows from a table or view in SQL Server.

-- Syntax for SQL Server and Azure SQL Database
[ WITH <common_table_expression> [ ,...n ] ] 
DELETE  
    [ TOP ( expression ) [ PERCENT ] ]  
    [ FROM ]  
    { { table_alias 
      | <object>  
      | rowset_function_limited  
      [ WITH ( table_hint_limited [ ...n ] ) ] }  
      | @table_variable 
    } 
    [ <OUTPUT Clause> ] 
    [ FROM table_source [ ,...n ] ]  
    [ WHERE { <search_condition>  
            | { [ CURRENT OF  
                   { { [ GLOBAL ] cursor_name }  
                       | cursor_variable_name  
                   }  
                ] 
              } 
            }  
    ]  
    [ OPTION ( <Query Hint> [ ,...n ] ) ]  
[; ] 
 
<object> ::= 
{  
    [ server_name.database_name.schema_name.  
      | database_name. [ schema_name ] .  
      | schema_name. 
    ] 
    table_or_view_name  
} 




















WITH <common_table_expression>
Specifies the temporary named result set, also known as common table expression, defined within the scope of the DELETE statement. The result set is derived from a SELECT statement.

Common table expressions can also be used with the SELECT, INSERT, UPDATE, and CREATE VIEW statements.

TOP (expression) [PERCENT ]
Specifies the number or percent of random rows that will be deleted. Expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

DELETE TOP (20)   
FROM Purchasing.PurchaseOrderDetail  
WHERE DueDate < '20020701';  
GO

If you have to use TOP to delete rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement. The following query deletes the 10 rows of the PurchaseOrderDetail table that have the earliest due dates. To ensure that only 10 rows are deleted, the column specified in the subselect statement (PurchaseOrderID) is the primary key of the table. Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.
 
DELETE FROM Purchasing.PurchaseOrderDetail  
WHERE PurchaseOrderDetailID IN  
   (SELECT TOP 10 PurchaseOrderDetailID   
    FROM Purchasing.PurchaseOrderDetail   
    ORDER BY DueDate ASC);  
GO


FROM
An optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.

DELETE FROM Sales.SalesPersonQuotaHistory; 
GO
DELETE Sales.SalesPersonQuotaHistory; 
GO

Table_alias
The alias specified in the FROM table_source clause representing the table or view from which the rows are to be deleted.

Server_name
Applies to: SQL Server 2008 through SQL Server 2016.
The name of the server (using a linked server name or the OPENDATASOURCE function as the server name) on which the table or view is located. If server_name is specified, database_name and schema_name are required.

Database_name
The name of the database.

Schema_name
The name of the schema to which the table or view belongs.

Table_or view_name
The name of the table or view from which the rows are to be removed.

A table variable, within its scope, also can be used as a table source in a DELETE statement.
The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view definition.


Rowset_function_limited
Applies to: SQL Server 2008 through SQL Server 2016.
Either the OPENQUERY or OPENROWSET function, subject to provider capabilities.

WITH ( <table_hint_limited> [... n] )
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed.

<OUTPUT_Clause>
Returns deleted rows, or expressions based on them, as part of the DELETE operation. The OUTPUT clause is not supported in any DML statements targeting views or remote tables.
 
DELETE Sales.ShoppingCartItem  
OUTPUT DELETED.*   
WHERE ShoppingCartID = 20621;

FROM table_source
Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause.
This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.
 
DELETE FROM Sales.SalesPersonQuotaHistory   
FROM Sales.SalesPersonQuotaHistory AS spqh  
INNER JOIN Sales.SalesPerson AS sp  
ON spqh.BusinessEntityID = sp.BusinessEntityID  
WHERE sp.SalesYTD > 2500000.00;  
GO 


WHERE
Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table.
There are two forms of delete operations based on what is specified in the WHERE clause:
Searched deletes specify a search condition to qualify the rows to delete. For example, WHERE column_name = value.

Positioned deletes use the CURRENT OF clause to specify a cursor. The delete operation occurs at the current position of the cursor. This can be more accurate than a searched DELETE statement that uses a WHERE search_condition clause to qualify the rows to be deleted. A searched DELETE statement deletes multiple rows if the search condition does not uniquely identify a single row.

DELETE FROM Production.ProductCostHistory  
WHERE StandardCost > 1000.00;  
GO

<search_condition>
Specifies the restricting conditions for the rows to be deleted. There is no limit to the number of predicates that can be included in a search condition.

CURRENT OF
Specifies that the DELETE is performed at the current position of the specified cursor.

GLOBAL
Specifies that cursor_name refers to a global cursor.

cursor_name
Is the name of the open cursor from which the fetch is made. If both a global and a local cursor with the name cursor_name exist, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. The cursor must allow updates.

DECLARE complex_cursor CURSOR FOR 
    SELECT a.BusinessEntityID 
    FROM HumanResources.EmployeePayHistory AS a 
    WHERE RateChangeDate <>  
         (SELECT MAX(RateChangeDate) 
          FROM HumanResources.EmployeePayHistory AS b 
          WHERE a.BusinessEntityID = b.BusinessEntityID) ; 
OPEN complex_cursor; 
FETCH FROM complex_cursor; 
DELETE FROM HumanResources.EmployeePayHistory 
WHERE CURRENT OF complex_cursor; 
CLOSE complex_cursor; 
DEALLOCATE complex_cursor; 
GO

Cursor_variable_name
The name of a cursor variable. The cursor variable must reference a cursor that allows updates.

OPTION ( <query_hint> [ ,... n] )
Keywords that indicate which optimizer hints are used to customize the way the Database Engine processes the statement. 


To delete all the rows in a table, use TRUNCATE TABLE. TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources. TRUNCATE TABLE has restrictions, for example, the table cannot participate in replication.

Use the @@ROWCOUNT function to return the number of deleted rows to the client application.

You can implement error handling for the DELETE statement by specifying the statement in a TRY…CATCH construct.
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.

When a DELETE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the Database Engine handles these errors as if SET ARITHABORT is set ON. The rest of the batch is canceled, and an error message is returned.













DELETE can be used in the body of a user-defined function if the object modified is a table variable.

When you delete a row that contains a FILESTREAM column, you also delete its underlying file system files. The underlying files are removed by the FILESTREAM garbage collector. For more information, see Access FILESTREAM Data with Transact-SQL.

The FROM clause cannot be specified in a DELETE statement that references, either directly or indirectly, a view with an INSTEAD OF trigger defined on it. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).



When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement. See the Examples section that follows in this topic.

TOP cannot be used in a DELETE statement against partitioned views.

By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. For more information, see Table Hints (Transact-SQL).

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

To delete rows in a heap and deallocate pages, use one of the following methods.
Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).

Use TRUNCATE TABLE if all rows are to be deleted from the table.

Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.

Empty pages can be removed from a heap at any time by using the ALTER TABLE <table_name> REBUILD statement.

The DELETE statement is always fully logged.

Permissions
DELETE permissions are required on the target table. SELECT permissions are also required if the statement contains a WHERE clause.

DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.




Truncate:
Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

 

Syntax

Transact-SQL
-- Syntax for SQL Server and Azure SQL Database  
  
TRUNCATE TABLE   
    [ { database_name .[ schema_name ] . | schema_name . } ]  
    table_name  
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }   
    [ , ...n ] ) ) ]  
[ ; ]  
  
<range> ::=  
<partition_number_expression> TO <partition_number_expression>  
 
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Arguments

database_name
Is the name of the database.

schema_name
Is the name of the schema to which the table belongs.

table_name
Is the name of the table to truncate or from which all rows are removed. table_name must be a literal. table_name cannot be the OBJECT_ID()function or a variable.

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )
Applies to: SQL Server (SQL Server 2016 through current version)
Specifies the partitions to truncate or from which all rows are removed. If the table is not partitioned, the WITH PARTITIONS argument will generate an error. If the WITH PARTITIONS clause is not provided, the entire table will be truncated.
<partition_number_expression> can be specified in the following ways:
·         Provide the number of a partition, for example: WITH (PARTITIONS (2))
·         Provide the partition numbers for several individual partitions separated by commas, for example: WITH (PARTITIONS (1, 5))
·         Provide both ranges and individual partitions, for example: WITH (PARTITIONS (2, 4, 6 TO 8))
·         <range> can be specified as partition numbers separated by the word TO, for example: WITH (PARTITIONS (6 TO 8))
To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).

 

Remarks

Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

·         Less transaction log space is used.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

·         Fewer locks are typically used.
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 (including a schema (SCH-M) lock) and page but not each row.

·         Without exception, zero pages are left in the table.
After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE 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.

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

 

Restrictions

You cannot use TRUNCATE TABLE on tables that:
·         Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
·         Participate in an indexed view.
·         Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

 

Truncating Large Tables

Microsoft SQL Server has the ability to drop or truncate tables that have more than 128 extents without holding simultaneous locks on all the extents required for the drop.

 

Permissions

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.















































Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

·         Less transaction log space is used.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

If you mistakenly execute a TRUNCATE statement, it is much more difficult to recover, and you may loose data in the process.  The TRUNCATE command does log the pages it removes, so it is possible to recover the pages using some advanced code.

·         Fewer locks are typically used.
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 (including a schema (SCH-M) lock) and page but not each row.

·         Without exception, zero pages are left in the table.
After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

·         Identity Column value reset to Seed Value.
TRUNCATE will reset any identity columns to the default seed value.  This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns.  After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1.  DELETE will not do this.  In the same scenario, if you DELETEd your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

·         Trigger Firing.
You want to remove rows from a table without activating the table’s after delete trigger use TRUNCATE operation.
Whereas Delete operation fires Delete triggers defined on tables.

·         Trigger Firing.
Truncate is a DDL statement
Delete is a DML statement

·         Rollback
Rollback is possible for Delete operation
Rollback is not possible for truncate operation

So we can rollback DELETE as well TRUNCATE if the commands are started inside a transaction and there is no difference between DELETE and TRUNCATE if we are talking about rollback. 



at

No comments:

Post a Comment