IntroductionColumnstores 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 viewFor 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.
ConclusionIt'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.