Delete all rows in table

Question

Normaly i would do a delete * from XXX but on this table thats very slow, it normaly has about 500k to 1m rows in it ( one is a varbinary(MAX) if that mathers ).

Basicly im wondering if there is a quick way to emty the table of all content, its actualy quicker to drop and recreate it then to delete the content via the delete sql statement

The reason i dont want to recreate the table is because its heavly used and delete/recreate i assume will destroy indexs and stats gathered by sql server

Im also hoping there is a way to do this because there is a "clever" way to get row count via sys.sysindexes , so im hoping there is a equaly clever way to delete content

Answer 1

Truncate table is faster than delete * from XXX. Delete is slow because it works one row at a time. There are a few situations where truncate doesn't work, which you can read about on MSDN.

Answer 2

Truncate table in MS Sql Server

Truncate table in Mysql

Answer 3

I would suggest using TRUNCATE TABLE, it's quicker and uses less resources than DELETE FROM xxx

Here's the related MSDN article

Answer 4

As other have said, TRUNCATE TABLE is far quicker, but it does have some restrictions (taken from here):

You cannot use TRUNCATE TABLE on tables that:

- Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
- Participate in an indexed view.
- Are published by using transactional replication or merge replication.

For tables with one or more of these characteristics, use the DELETE statement instead.

The biggest drawback is that if the table you are trying to empty has foreign keys pointing to it, then the truncate call will fail.

Answer 5

You can rename the table in question, create a table with an identical schema, and then drop the original table at your leisure.

See the MySQL 5.1 Reference Manual for the [RENAME TABLE][1] and [CREATE TABLE][2] commands.

RENAME TABLE tbl TO tbl_old;

CREATE TABLE tbl LIKE tbl_old;

DROP TABLE tbl_old; -- at your leisure

This approach can help minimize application downtime.