Is it faster to drop a table, recreate it and reindex it than to simply delete all rows from it in SQL Server?

Question

Let's say we have a table that has 2 million rows. It has two nonclustered indices on it. Generally speaking, would it be faster to drop it, recreate it with new data and re-apply the indices than to delete from it all its rows and then do an insert of new data?

Note: I am trying to avoid using trunc as it requires sysadmin priv.

Answer 1

Try TRUNCATE TABLE TableName the TRUNCATE statement is very fast, and a better approach than dropping the table, or using DELETE FROM

Permissions: From the MSDN about TRUNCATE:

The minimum permission required is ALTER on table_name. TRUNCATE TABLE
permissions default to the table owner, members of the sysadmin fixed server
role, and the db_owner and db_ddladmin fixed database roles,
and are not transferable.

However, you can incorporate the TRUNCATE TABLE statement within a module, such
as a stored procedure, and grant appropriate permissions to the module using the
EXECUTE AS clause. For more information, see Using EXECUTE AS to Create Custom
Permission Sets.

So you do not need sysadmin credentials to accomplish this.

Answer 2

James, you don't need to drop it. If you don't care about transaction logs on the delete just use Truncate table, as this won't generate transaction logs and therefore is very quick. http://msdn.microsoft.com/en-us/library/ms177570.aspx