sql-server - 删除表、重新创建表并重新索引是否比在 SQL Server 中简单地删除表中的所有行更快？

Question

假设我们有一个包含 200 万行的表。它有两个非聚集索引。一般来说，删除它、用新数据重新创建它并重新应用索引会比删除更快吗？ 它的所有行，然后插入新数据？

注意:我试图避免使用 trunc 因为它需要 sysadmin priv。

Answer 1

尝试 TRUNCATE TABLE TableName TRUNCATE语句非常快，并且是比删除表或使用 DELETE FROM

更好的方法

权限:来自 MSDN 关于 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.

因此您不需要 sysadmin 凭据来完成此操作。