CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
deutschZuid
deutschZuid

Reputation: 1058

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

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.

Upvotes: 4

Views: 4717

Answers (4)

Nonym
Nonym

Reputation: 6299

I've worked with a similar issue and my table had around 8 million rows, with 43 columns. Dropping the indexes, Truncating the table, Performing a bulk insert, Recreating the indexes -- it still performs pretty fast, considering the amount of data, and I do perform this task during off-peak hours.

If you want to avoid using TRUNCATE, you may create a procedure to perform the truncate, and use a user with the right permissions. That is, if granting permissions to the job user is the reason you want to avoid using the truncate command:

CREATE PROCEDURE dbo.TruncateTableName
WITH EXECUTE AS 'SqlUserWithTruncatePermission'
AS
BEGIN
    TRUNCATE TABLE TableName
END
GO

If you want to try this out, you can read more at:

http://msdn.microsoft.com/en-us/library/ms188354(v=SQL.90).aspx

Likewise, for other actions that require more permissions than you want, you can follow suit...

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

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

That is completely inaccurate. The TRUNCATE statement requires ALTER TABLE permission:

The minimum permission required is ALTER on table_name.

So this is exactly the same permission it would be required to DROP INDEX and re-create, or to ALTER INDEX ... DISABLE the index and re-enable:

To execute DROP INDEX, at a minimum, ALTER permission on the table or view is required. To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

So the faster is to TRUNCATE the table, then disable the indexes, insert the data, then enable back the indexes (which requires a rebuild of the indexes). But it would hardly worth doing such disable/enable game for a mere 2M rows. For such a small job simply truncate the table and then bulk insert the rows.

Upvotes: 3

Adam Wenger
Adam Wenger

Reputation: 17570

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.

Upvotes: 4

Gus
Gus

Reputation: 10659

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

Upvotes: 3

Related Questions