June 15, 2018 at 1:28 am
Clustered Columnstore indexes (CCI) don't support foreign & primary keys.
What use are they in a DW setting, unless all of my tables with CCIs are standalone tables.
Foreign keys are needed to define the star-schema and the fact tables have to have a primary key.
Am I missing something here or are CCIs really of very limited usage?
June 21, 2018 at 1:47 pm
Hey Sean,
A few thoughts...columnstore indexes are all about performance. They are great in a warehouse context (or even OLTP context - see link below) where we need to aggregate a ton of rows. I'm thinking about measures in fact tables. I'd encourage you to not toss these indexes. Instead, give them some more research.
Next, in 2016, everything you are looking for is supported. Here is a real simple example that works
create table dbo.dim (id int not null primary key)
go
create table dbo.fact (id int not null, dimid int, measure int)
go
create clustered columnstore index idx_col on dbo.fact
go
alter table dbo.fact add constraint pk_fact primary key (id)
go
alter table dbo.fact add constraint fk_fact_dim foreign key (dimid) references dbo.dim (id)
go
Finally, despite what some would recommend for performance reasons, I always include foreign key constraints in my warehouse tables. No matter how good integration code is, I always want an extra layer of protection to ensure integrity. That said however, you don't need foreign key constraints to have a fact-dimension relationship.
Hope this helps a little and encourages you to keep exploring columnstore indexes 🙂
June 21, 2018 at 10:52 pm
Many thanks for this and for the link Heb1014. I shall check it out.
Sean.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply