Is it faster to roll back a table, recreate it, and reindex it, rather than just delete all rows from it in SQL Server?

Question

Say we have a table with 2 million rows. It has two non-clustered indexes. Generally speaking, would it be faster to drop , recreate with new data and re-apply indexes than to remove from all its rows, and then do insert new data?

Note. I try to avoid using trunc as it requires sysadmin priv.

Answer 1

Try TRUNCATE TABLE TableName TRUNCATE statement is a very quick and better approach than deleting a table or using DELETE FROM

Permissions: from MSDN near 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. 

You do not need sysadmin credentials for this.

Answer 2

James, you don’t have to drop him. If you do not need the transaction logs in the delete file, just use the Truncate table, as this will not lead to the creation of transaction logs and therefore very fast. http://msdn.microsoft.com/en-us/library/ms177570.aspx

Answer 3

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

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

The minimum permission required is ALTER on table_name.

So, this is the same permission that DROP INDEX will need to be recreated, or ALTER INDEX ... DISABLE index and re-enable:

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

Thus, the faster TRUNCATE is in the table, then turn off the indexes, insert the data, and then turn back on the indexes (which require rebuilding the indexes). But it is hardly worth making such a game with disabling / enabling for simple 2M strings. For such a small job, just crop the table, and then insert the rows into the arrays.

Answer 4

I worked with a similar problem, and in my table there were about 8 million rows with 43 columns. Deleting indexes, Truncating a table, Performing a bulk insert, Playing indexes - it is still pretty fast, given the amount of data, and I perform this task after hours.

If you want to avoid using TRUNCATE , you can create a procedure to perform the crop and use the user with the correct permissions. That is, if granting permission to the job user is the reason why 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, you can find out more:

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

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