SQL Delete Statement

The DELETE Statement is used to delete rows from a table.

Syntax of a SQL DELETE Statement

DELETE FROM table_name [WHERE condition];

table_name -- the table name which has to be updated.

NOTE: The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

SQL DELETE Example

To delete an employee with id 100 from the employee table, the sql delete query would be like,

DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like,

DELETE FROM employee;

SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

SQL TRUNCATE Statement Example

To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE employee;

Difference between DELETE and TRUNCATE Statements:
DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

SQL DROP Statement:

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using DROP command. When a table is dropped all the references to the table will not be valid.
Syntax to drop a sql table structure:

DROP TABLE table_name;

SQL DROP Statement Example

To drop the table employee, the query would be like

DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if you want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.

TRUNCATEDELETE TRUNCATE is a DDL command DELETE is a DML command TRUNCATE is executed using a table lock and whole table is locked for remove all records. DELETE is executed using a row lock, each row in the table is locked for deletion. We cannot use Where clause with TRUNCATE. We can use where clause with DELETE to filter & delete specific records. TRUNCATE removes all rows from a table. The DELETE command is used to remove rows from a table based on WHERE condition. Minimal logging in transaction log, so it is performance wise faster. It maintain the log, so it slower than TRUNCATE. 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 DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row Identify column is reset to its seed value if table contains any identity column. Identity of column keep DELETE retain the identity To use Truncate on a table you need at least ALTER permission on the table. To use Delete you need DELETE permission on the table. Truncate uses the less transaction space than Delete statement. Delete uses the more transaction space than Truncate statement. Truncate cannot be used with indexed views Delete can be used with indexed views Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table Keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages. TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions. When we run truncate command to remove all rows of table then it actually doesn’t removes any row, rather it deallocates the data pages. In case of Truncate triggers will not be fired because no modification takes place, we have just deallocated the data pages not deleted any row from table. Delete activates a trigger because the operation are logged individually. When we execute Delete command, DELETE trigger will be initiated if present. Delete is a DML command and it deletes the data on row-by-row basis from a table. Which means delete is modifying the data by deleting it from the table. Triggers are fired when a DML statement executed on a table, so trigger will be fired in case of Delete command execution. - See more at: http://www.softwaretestingclass.com/difference-between-truncate-and-delete-statement-in-sql-server/#sthash.uqvy43SF.dpuf

Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments. Stay tuned and stay connected for more technical updates."

What is “Difference between TRUNCATE and DELETE statement in SQL Server” is most common question asked in the interview. I have observed that the answer of this question is varies based on experience and understanding of the SQL server. So in this article I am explaining the significant difference between Delete vs Truncate. - See more at: http://www.softwaretestingclass.com/difference-between-truncate-and-delete-statement-in-sql-server/#sthash.uqvy43SF.dpuf

.Net Learning made easy

my own pages

Thursday, February 5, 2015

Difference between TRUNCATE and DELETE statement in SQL Server