自动删除并重新创建当前索引 [英] Automatically Drop and Recreate current indexes

问题描述

我正在努力提供一种方法，以允许批量更新我们的表(每个更新大于1M个新行或更新的行)，并且对删除当前索引并在更新后重新创建它们感兴趣.

I'm working on providing a method to allow for bulk updating our tables ( greater than 1M new or updated rows per update) and was interested in dropping the current indexes and recreating them after the updates.

我想知道是否有人有脚本来提供这些操作的松散耦合，以便如果索引随时间变化，则更新过程不会改变.

I was wondering if anyone has a script to provide loose coupling of these operations so that if the indexes change over time, the update process does not change.

这似乎是社区可能已经解决的事情之一.

It seems like this is one of those things that the community has already probably solved.

推荐答案

我有用于查询系统表的脚本，以捕获所有非聚集索引并禁用，然后在完成后进行重建.以下内容适用于标准版，如果您使用的是企业版，请添加ONLINE选项.

I have script that I use to query the system tables to capture all non-clustered indexes and disable then rebuild upon completion. The below is for use on standard edition, if you are on enterprise I would add the ONLINE option.

禁用

DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT 
    @sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] DISABLE; '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'

EXEC (@sql)

重建

DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT 
    @sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] REBUILD WITH (FILLFACTOR = 80); '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'

EXEC (@sql);

我喜欢这种方法，因为它非常可定制，因为您可以根据条件排除/包含某些表以及避免使用游标.您也可以将EXEC更改为PRINT，然后查看将执行的代码并手动运行它.

I like this method as it is very customizable as you can exclude/include certain tables based on the conditions as well as avoiding a cursor. Also you can change the EXEC to a PRINT and see the code that will execute and manually run it.

排除表格的条件

AND o.name NOT IN ('tblTest','tblTest1');

这篇关于自动删除并重新创建当前索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！