SQL Server Portal

sp_MsForEachTable is one of my favorite undocumented stored procedures. Even though it is undocumented (support not available) it is very handy stored procedure. I remember giving one script using sp_MsForEachTable to my team to built all indexes via a single line of code. Given below is the script.

--Given below script is not compatible with Columnstore Index
USE AdventureWorks2012;
GO
EXEC sp_MSforeachtable @command1="print '?'"
, @command2="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=80)"

But this script broke down today while running on SQL Server 2012 with an error.

Let’s discuss this error in detail:
Message Number: 35327

Severity : 16

Error Message: ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

Error Generation:
Let me create a columnstore index on a table to demonstrate this error.

USE AdventureWorks2012;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
([ProductID],
[OrderQty],
[UnitPrice]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Once columnstore index is created, lets execute the given below script.

USE AdventureWorks2012;
GO
EXEC sp_MSforeachtable @command1="print '?'"
, @command2="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=80)"
--OUTPUT

Msg 35327, Level 16, State 1, Line 1
ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is
not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

Ooopps…… I am unable to execute it properly.

Resolution:

The reason behind this error is that once the above script tried to build columnstore index using fill factor, it generated error because it cannot build columnstore index using fill factor.

Two different methods to fix this error are :

• Using Cursor
• Using sp_MSforeachtable

Using Cursor

This method is recommended because there is no undocumented command involved in this solution and it uses cursor to build each index. Here is the solution.

Using sp_MSforeachtable

This method uses shortest possible solution. I modified above script and now it is compatible with columnstore index. It can build columnstore index without fill factor but the rest of the indexes will be filled with fill factor. Given below is the script.

USE AdventureWorks2012;
GO
EXECUTE sp_msForEachTable ' Print ''?'';SET QUOTED_IDENTIFIER ON
;IF EXISTS (Select * from sys.indexes
Where object_id = object_id(''?'') And type=6)
ALTER INDEX ALL ON ? REBUILD ELSE
ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80)'
--OUTPUT

Conclusion :
Remember, whenever you build columnstore index either all indexes together or discretely, never build columnstore index with fill factor.