Clustered ColumnStore Indexes are a powerful feature introduced in SQL Server that can greatly improve the performance of data retrieval and storage. In this blog post, we will explore the concept of Clustered ColumnStore Indexes and discuss how they can be used effectively in your SQL Server environment.

One of my customers recently brought up the topic of Clustered ColumnStore Indexes during a conversation about upgrading from SQL Server 2012 to SQL Server 2014. They were particularly interested in the benefits of using Clustered ColumnStore Indexes, as it allows for updatable data with its implementation.

During the conversation, a developer encountered an error message related to Clustered ColumnStore Indexes and sought my assistance. The error message read: “Msg 1907, Level 16, State 1, Line 5 Cannot recreate index ‘PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber’. The new index definition does not match the constraint being enforced by the existing index.”

To understand this error and its resolution, let’s walk through an example. We will create a table with constraints and attempt to create a Clustered ColumnStore Index:

CREATE DATABASE ColumnStoreDemos
GO

USE ColumnstoreDemos
GO

-- Create a normal table.
IF OBJECT_ID ('FactResellerSales_Trim') IS NOT NULL
    DROP TABLE FactResellerSales_Trim
GO

CREATE TABLE [dbo].FactResellerSales_Trim (
    [ProductKey] [int] NOT NULL,
    [OrderDateKey] [int] NOT NULL,
    [DueDateKey] [int] NOT NULL,
    [ShipDateKey] [int] NOT NULL,
    [ResellerKey] [int] NOT NULL,
    [EmployeeKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [SalesTerritoryKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar] (20) NOT NULL,
    [SalesOrderLineNumber] [tinyint] NOT NULL,
    CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY CLUSTERED (
        [SalesOrderNumber] ASC,
        [SalesOrderLineNumber] ASC
    )
) ON [PRIMARY]
GO

-- Attempt to create a Clustered ColumnStore Index
CREATE CLUSTERED columnstore INDEX [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] ON FactResellerSales_Trim WITH (DROP_EXISTING = ON)
GO

Executing the above code will result in the error message mentioned earlier. This error occurs because primary keys or foreign key constraints are not supported on a table with a clustered columnstore index.

To resolve this issue, we need to drop the constraints before creating the clustered columnstore index:

ALTER TABLE FactResellerSales_Trim DROP CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber]
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_FactResellerSales] ON FactResellerSales_Trim
GO

After dropping the constraint and creating the clustered columnstore index, we can verify its successful creation using the following query to DMVs:

SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID ('FactResellerSales_Trim')

Clustered ColumnStore Indexes are a valuable tool for optimizing data storage and retrieval in SQL Server. However, it is important to be aware of the limitations and considerations when working with them. By understanding the error messages and their resolutions, you can effectively utilize Clustered ColumnStore Indexes in your SQL Server environment.

I hope this blog post has provided you with a clear understanding of Clustered ColumnStore Indexes in SQL Server. If you encounter any issues or have further questions, feel free to reach out to me. Happy coding!