2

I have been assigned the task of designing a dimension or fact table for an EDW.

I know that a SQL Server table supports only one rowstore clustered index (conventional clustered index) and that you can create as many rowstore nonclustered indexes (conventional non-clusterd indexes) as required.

I would like to know if clustered columnstore and nonclustered indexes can be added to the same SQL Server table.

For instance, can I create a table with one rowstore clustered index (clusterd index), two nonclustered rowstore indexes (non-clustered indexes), and one nonclustered columnstore index?

Versions of the SQL Server database engine where we store our data: 2014, 2016, and 2017.

  • Is it correct that you wrote I'm concerned with dimension or fact table design for EDW but that you possibly meant I have been assigned the task of designing a dimenstion or fact table for an EDW ? – hot2use Feb 28 '18 at 15:13
3

This can easily be done in SQL Server 2016. You can create nonclustered rowstore indexes and one nonclustered columnstore index on a table even if that table already has a clustered rowstore index.

This can also be done in SQL Server 2014, but data cannot be modified in the table after the NCCI is created.

I suggest reading the documentation and trying things out in SSMS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.