Non Clustered Columnstore Indexes and Primary/Foreign keys

Question

Documentation says that we can't create nonclustered columnstore indexes in primary key or foreign key columns

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql

" A table with a nonclustered columnstore index can have unique constraints, primary key constraints, or foreign key constraints, but the constraints cannot be included in the nonclustered columnstore index. "

but on my tests, It is working:

CREATE TABLE SimpleTable(  
ProductKey [int] primary key,   
OrderDateKey [int] NOT NULL,   
DueDateKey [int] NOT NULL,   
ShipDateKey [int] NOT NULL) 

GO

insert into SimpleTable values (1,2,3,4)

CREATE nonclustered COLUMNSTORE INDEX IXTEST ON SimpleTable(ProductKey)

What am I missing here? Is documentation correct?

Here is my SQL Server version

Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64) Jul 16 2017 18:08:49 Copyright (c) Microsoft Corporation Developer Edition

Regards

Chris Mack Chris Mack · Accepted Answer · 2017-11-14T02:37:47

I'm not sure on the significance of the statement, as the constraints will still exist on the table, but I imagine, purely based on the language, that what is being said is that the constraints cannot be included, but the columns can.

Non Clustered Columnstore Indexes and Primary/Foreign keys

1 Answers