SQL Server series: Delete statements

Deleting data deletes some or all of the records in the table, and you can delete one or more records by specifying delete criteria when deleting. If you do not specify a delete condition, the DELETE statement deletes all records in the table and empties the data table.

Delete syntax:

[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}

1. Delete one or more records according to the specified criteria

DELETE  from [dbo]. [Product] WHERE = 1

2. Delete all records in the table

You can delete all records in a table by using a DELETE statement without a WHERE clause.

DELETE  from [dbo]. [Product]

Deleting all records in a table can also use the TRUNCATE statement:

TRUNCATE TABLE [dbo]. [Product]

3. Use TOP to limit the number of rows deleted

When the TOP (n) clause is used with the delete, the delete operation is performed on the randomly selected n rows.

DELETE TOP (tenfrom[dbo]. [Product]

SQL Server series: Delete statements