DELETE permanently deletes rows from a table.
DELETE can delete one or more records in a table.
Use the WHERE clause to DELETE only specific records.
Remove customer Elizabeth Lincoln who lives in Canada.
DELETE Customer
WHERE FirstName = 'Elizabeth'
AND LastName = 'Lincoln'
AND Country = 'Canada'
Be sure to include a WHERE clause or else all records are deleted from the table.
DELETE syntax.
DELETE table-name
DELETE syntax with WHERE.
DELETE table-name WHERE condition
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
DELETE Product
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
DELETE Product
WHERE UnitPrice > 50
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
DELETE Customer
WHERE Id = 21
This is a common scenario in which a single record is deleted.
This may seem like a strange question, but it is an important one.
First off, the DELETE command physically removes rows from a table.
This is called a hard-delete. Once deleted, the data cannot be recovered.
The opposite is a soft-delete in which a row is flagged as deleted but it is not physically deleted. This is accomplished with a BIT column named 'deleted'.
The advantage is that deleted data is recoverable.
The disadvantage is that queries must include a check for the deleted flag.
Another in-between model copies the deleted rows to a backup/recovery table before being deleted. These rows can then be recovered if necessary. The backup operation can be implemented quite elegantly with triggers.
Deleting a single row with DELETE is generally fast enough.
However, deleting multiple rows can be slow because it is a transacted operation.
This means that all row are written to a SQL Server transaction log before the rows are actually deleted.
To delete all rows in a table use
TRUNCATE rather than DELETE.
To delete most rows in a table use SELECT INTO to save the data that needs to be preserved. Then use TRUNCATE to empty the table. Finally, move the saved data back to the original table.
To delete a smaller subset of rows in a table you can DELETE rows in
chunks (groups of rows), so that the transactions are kept at a manageable size.,
With all these operations consider temporarily removing indexes and constraints on
the table. Then execute the DELETE procedure.
When completed re-apply the indexes and constraints.
Before applying any of the above be sure to research and test which approach is best for your situation.
DELETE is transacted, meaning each row is logged before deletion.
TRUNCATE is not transacted and therefore much faster.
Even with large tables, TRUNCATE can be very fast.
Yes. All DELETE operations are automatically transacted.
This is true, whether deleting 1 or a 10,000 rows.
These operations are transacted because if anything goes wrong, the changes can be rolled back (undone), and data integrity is maintained.
DELETE operations can be rolled back (undone).
Use BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN to manage SQL transactions.
