DELETE 命令

DELETE [ FROM ]
   { table_name WITH ( <table_hint_limited> [ ...n ] )
      | view_name
      | rowset_function_limited
   }

[ FROM { <table_source> } [ ,...n ] ]

   [ WHERE
      { <search_condition>
        | { [ CURRENT OF
                {

                   { [ GLOBAL ] cursor_name }
                   | cursor_variable_name
                }
             ]

          }
      }
   ]
   [ OPTION ( <query_hint> [ ,...n ] ) ]

<table_source> ::=
   table_name
      [ [ AS ] table_alias ]
      [ WITH ( <table_hint> [ ,...n ] ) ]
   | view_name [ [ AS ] table_alias ]
   | rowset_function [ [ AS ] table_alias ]
   | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
   | <joined_table>

<joined_table> ::=
<table_source> <join_type> <table_source>
ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| <joined_table>

<join_type> ::=
    [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
    [ <join_hint> ]
    JOIN

<table_hint_limited> ::=
{ FASTFIRSTROW
   | HOLDLOCK
   | PAGLOCK
   | READCOMMITTED
   | REPEATABLEREAD
   | ROWLOCK
   | SERIALIZABLE
   | TABLOCK
   | TABLOCKX
   | UPDLOCK
}

<table_hint> ::=
{ INDEX ( index_val [ ,...n ] )
   | FASTFIRSTROW
   | HOLDLOCK
   | NOLOCK
   | PAGLOCK
   | READCOMMITTED
   | READPAST
   | READUNCOMMITTED
   | REPEATABLEREAD
   | ROWLOCK
   | SERIALIZABLE
   | TABLOCK
   | TABLOCKX
   | UPDLOCK
}

<query_hint> ::=
{ { HASH | ORDER } GROUP
   | { CONCAT | HASH | MERGE } UNION
   | FAST number_rows
   | FORCE ORDER
   | MAXDOP
   | ROBUST PLAN
   | KEEP PLAN
}

参数	意义
TABLE_NAME	可以列出多个表，但仅在第一个表中删除，其它用于设置条件

TRUNCATE 命令

删除所有记录，比用 DELETE 更快。

因为此命令释放所有的索引分布页，所以在加入新行到表中后要运行 UPDATE STATISTICS。

TRUNCATE TABLE table_name

DROP 命令

删除表：

DROP TABLE table_name

删除视图：

DROP VIEW

删除存储过程：

DROP PROCEDURE