SQL : ColumnStore (part III) : enforce uniqueness (SQL Server 2014)

Introduction

Columnstores in SQL Server 2014 can not enforce primary keys and you can't create an unique non clustered index in contrast with SQL server 2016, where you are able to create an unique non clustered index on the ColumnStore table. That's a pity because I have to work with SQL Server 2014. Now, how can you enforce a semi primary key on a ColumnStore table? Well, there seems to be a way to do that with indexed views together with an unique clustered index. In this blogpost I'll show you the way. For this blogpost I've used the blogpost of MSDN where you can find more information about this.

Enforce uniqueness with Indexed view

For this blogpost I've created a simple table.

       
/*
DROP TABLE testIndexedVIEW
GO
*/
CREATE TABLE testIndexedVIEW
(TestID int)
GO     
 

The next step is creating a ColumStore table with the following statement:

       
CREATE CLUSTERED COLUMNSTORE INDEX CCI_testIndexedVIEW ON testIndexedVIEW;  
GO        
 

Next step is building the indexed view on this table (WITH SCHEMABINDING):

       
/* 
DROP VIEW dbo.testIndexedViewUniqueness
GO
*/

CREATE VIEW dbo.testIndexedViewUniqueness
WITH SCHEMABINDING
AS 
SELECT TestID
FROM dbo.testIndexedVIEW
GO      
 

And now create a Unique Clustered Index on this Indexed view:

       

CREATE UNIQUE CLUSTERED INDEX UNCI_testIndexedViewUniqueness ON testIndexedViewUniqueness(TestID)
GO
       
 

That's it. The columnstore, indexed view and Unique Clustered Index are in place and now it's time to test this by inserting twice the same value in the column with the Unique Clustered Index.

       
INSERT INTO [dbo].testIndexedVIEW
           (TestID)
     VALUES
           (1)

INSERT INTO [dbo].testIndexedVIEW
           (TestID)
     VALUES
           (1)     
 

Resulting in an error message.

Level 14, State 1, Line 21
Cannot insert duplicate key row in object 'dbo.testIndexedVIEW' with unique index 'UNCI_testIndexedViewUniqueness'. The duplicate key value is (1).
The statement has been terminated.

Conclusion

It's not possible to create an Unique Clustered Index on a ColumnStore table in SQL Server 2014. With an Indexed View, together with a Unique Nonclustered index you can enforce the uniqueness of the key fields.

Greetz,

Hennie