Unique Key Constraints with Clustered ColumnStore Indexes in SQL Server 2014.

In SQL Server 2014, if you create clustered columnstore index on table, you are not allowed to create any other indexes, unique keys or primary key. SQL Server 2016 removes this limitation.

In order to enforce unique keys in SQL Server 2014, you can use indexed views.

Using the example of AdventureWorksDW

-- CREATE table For Clustered ColumnStore Index.

select * INTO [dbo].[FactResellerSalesCCIX] from [dbo].[FactResellerSales]

-- Create Clustered ColumnStore Index.

Create Clustered ColumnStore Index ccix_FactResellerSales on FactResellerSalesCCIX

-- Create View Containing Unique Key Columns.

CREATE VIEW  v_FactResellerSalesCCIX_Uniq

WITH SCHEMABINDING

AS SELECT [SalesOrderNumber], [SalesOrderLineNumber]

FROM [dbo].[FactResellerSalesCCIX]

-- Create Unique Clustered Index on the View.

CREATE UNIQUE CLUSTERED INDEX IX_v_FactResellerSalesCCIX_Uniq ON v_FactResellerSalesCCIX_Uniq([SalesOrderNumber], [SalesOrderLineNumber])

-- Test The Unique Key

INSERT INTO [dbo].[FactResellerSalesCCIX]

SELECT TOP 1 * FROM [dbo].[FactResellerSalesCCIX]

Msg 2601, Level 14, State 1, Line 11

Cannot insert duplicate key row in object 'dbo.v_FactResellerSalesCCIX_Uniq' with unique index 'IX_v_FactResellerSalesCCIX_Uniq'. The duplicate key value is (SO46339, 5).