增加小数列的精度和比例会影响SQL Server中现有索引吗？

发布于 2025-02-12 18:43:09 字数 774 浏览 1 评论 0原文

我在SQL Server上有一个超过1亿行的SQL Server，并且使用了大量使用。我正在考虑增加其中一列之一的小数精度和比例，该列也具有非群集索引。在更改精度/比例时，是否还建议在更新精度/比例之前删除索引，然后再重新创建索引（例如，在下面的摘要中类似）。基本上，不完全确定改变该领域的影响会对指数产生什么样的影响 - Drop + Recreation是过度的？

DROP INDEX IF EXISTS [IX_Index] ON [dbo].[TableName]
ALTER TABLE dbo.[TableName]
ALTER COLUMN DecimalField DECIMAL(14, 9) NOT NULL
                    
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name='IX_Index' AND object_id = OBJECT_ID('dbo.TableName'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[TableName]
    (
        DecimalField ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
END

原文

I have a table on SQL Server with over 100 million rows and quite heavily used. I'm looking at increasing the decimal precision and scale of one of the columns, which also has a non-clustered index. When changing precision/scale, is it also recommended to drop the index before updating precision/scale, followed by recreating an index (something like in the snippet below for example). Basically not entirely sure what sort of impact altering the field would have on the index - is the drop + recreation an overkill?

DROP INDEX IF EXISTS [IX_Index] ON [dbo].[TableName]
ALTER TABLE dbo.[TableName]
ALTER COLUMN DecimalField DECIMAL(14, 9) NOT NULL
                    
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name='IX_Index' AND object_id = OBJECT_ID('dbo.TableName'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[TableName]
    (
        DecimalField ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
END

分享到QQ

分享到微博