clustered columnstore index, foreign & primary keys

  • sean redmond

    SSCertifiable

    Points: 5739

    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?

  • heb1014

    Hall of Fame

    Points: 3772

    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 🙂

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics?view=sql-server-2017

  • sean redmond

    SSCertifiable

    Points: 5739

    Many thanks for this and for the link Heb1014. I shall check it out.
    Sean.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply