Microsoft SQL Server Getting started with Microsoft SQL Server DELETE All Rows


Example

DELETE
FROM Helloworlds

This will delete all the data from the table. The table will contain no rows after you run this code. Unlike DROP TABLE, this preserves the table itself and its structure and you can continue to insert new rows into that table.

Another way to delete all rows in table is truncate it, as follow:

TRUNCATE TABLE HelloWords

Difference with DELETE operation are several:

  1. Truncate operation doesn't store in transaction log file
  2. If exists IDENTITY field, this will be reset
  3. TRUNCATE can be applied on whole table and no on part of it (instead with DELETE command you can associate a WHERE clause)

Restrictions Of TRUNCATE

  1. Cannot TRUNCATE a table if there is a FOREIGN KEY reference
  2. If the table is participated in an INDEXED VIEW
  3. If the table is published by using TRANSACTIONAL REPLICATION or MERGE REPLICATION
  4. It will not fire any TRIGGER defined in the table

[sic]