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

User · Answer

I would suggest using TRUNCATE TABLE  it s quicker and uses less resources than DELETE FROM xxx  Here s the related MSDN article

User · Answer

Truncate table in MS Sql Server  Truncate table in Mysql

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Delete all rows in table

Examples related to sql-server

Examples related to tsql