fastest way to delete the content of a table

Question

I have a table that I use for caching json strings and once in a while, I need to clear it. What's the fastest way to clear the content of a table?

I'm using linq-to-sql at the moment but I'm wondering if there's not a one-liner type of command that just does the job faster than my L2S query.

Thanks for suggestions.

Take a look at this post: http://stackoverflow.com/questions/9103037/truncate-or-drop-and-create-table — Mark Kram, Feb 10 '12 at 23:41

score 2 · Accepted Answer · answered Feb 10 '12 at 23:39

2

the sql statement

truncate table [name of table]

answered Feb 10 '12 at 23:39

Jason Meckley

7,589
1
24
45

score 2 · Answer 2 · answered Feb 10 '12 at 23:43

2

context.ExecuteCommand("TRUNCATE TABLE Entity");

answered Feb 10 '12 at 23:43

Adrian Iftode

15,465
4
48
73

score 1 · Answer 3 · answered Feb 10 '12 at 23:39

1

The fastest way would definitely be using;

TRUNCATE TABLE <table name>

which will remove all rows, bypassing logging all individual deletes.

answered Feb 10 '12 at 23:39

Joachim Isaksson

176,943
25
281
294

score 1 · Answer 4 · answered Feb 11 '12 at 12:14

1

If there are no foreign key constraints, try using DROP TABLE and then just recreate it. I've seen that work even faster than TRUNCATE. You just have to script the creation of it too.

answered Feb 11 '12 at 12:14

Grant Fritchey

2,645
19
21

Please explain why dropping and creating would be any better than truncate. In terms of logging the drop create [has slightly higher overhead](http://dba.stackexchange.com/questions/4163/why-use-both-truncate-and-drop/7685#7685) as far as I could see as they both do the same work but the `drop` also does some extra deletes from system tables to remove all the metadata about the table and columns. – Martin Smith Feb 11 '12 at 12:17
As I said, I've seen the drop work faster in some cases. I'm not saying it's completely different. – Grant Fritchey Feb 11 '12 at 12:24
So in what specific cases will it work faster then? As it seems it is doing slightly more work. – Martin Smith Feb 11 '12 at 12:26
Unfortunately, I don't work at that company any more and I can't access the database to give you specifics. We had a situation with an otherwise unattached table that we needed to get rid of regularly & reload. Testing with both truncate & drop, we found drop was consistently faster. – Grant Fritchey Feb 11 '12 at 12:29
Maybe something to do with deferred drop? Do you remember the version of SQL Server? – Martin Smith Feb 11 '12 at 12:31
It was on 2000 & 2005. I don't know if they've upgraded since then or not. – Grant Fritchey Feb 11 '12 at 12:36

fastest way to delete the content of a table

4 Answers4