Description of the issue
Index Maintenance package fails when RESUMABLE = Y and a rebuild is attempted on an index that contains a computed column as a key field. This applies to both clustered and nonclustered indexes. Nonclustered indexes presents an additional consideration that operations there must also consider the clustered index since the clustered index is implicitly included with nonclustered index operations.
I believe the fix would be conditional logic related to RESUMABLE parameter to account for computed fields within the clustered index (for operations targeted at clustered index or nonclustered indexes) and nonclustered indexes (for the operations targeted at nonclustered indexes).
The particular error observed is as follows:
Resumable index operation for index 'IndexName' failed because the index contains the computed column 'ColumnName' as a key or partitioning column. If this is a non clustered index, the column will be implicitly included as a key column if it is part of the clustered index key.
SQL Server version and edition
Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64) Jul 27 2023 18:11:00 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Version of the script
2022-12-03 17:23:44
What command are you executing?
IndexOptimize (stored procedure)
What output are you getting?Resumable index operation for index 'IndexName' failed because the index contains the computed column 'ColumnName' as a key or partitioning column. If this is a non clustered index, the column will be implicitly included as a key column if it is part of the clustered index key.
Description of the issue Index Maintenance package fails when RESUMABLE = Y and a rebuild is attempted on an index that contains a computed column as a key field. This applies to both clustered and nonclustered indexes. Nonclustered indexes presents an additional consideration that operations there must also consider the clustered index since the clustered index is implicitly included with nonclustered index operations.
I believe the fix would be conditional logic related to RESUMABLE parameter to account for computed fields within the clustered index (for operations targeted at clustered index or nonclustered indexes) and nonclustered indexes (for the operations targeted at nonclustered indexes).
The particular error observed is as follows: Resumable index operation for index 'IndexName' failed because the index contains the computed column 'ColumnName' as a key or partitioning column. If this is a non clustered index, the column will be implicitly included as a key column if it is part of the clustered index key.
SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64) Jul 27 2023 18:11:00 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Version of the script 2022-12-03 17:23:44
What command are you executing? IndexOptimize (stored procedure)
What output are you getting? Resumable index operation for index 'IndexName' failed because the index contains the computed column 'ColumnName' as a key or partitioning column. If this is a non clustered index, the column will be implicitly included as a key column if it is part of the clustered index key.