I have two very large tables that need to be joined to produce a data extract. Both tables have clustered column store indexes and are partitioned.

DW Sentry advises me in the plan view that it thinks a NC index should be added, but without compression, it's likely to be a very large index. Does anyone know about the join characteristics for a columnstore?

closed as too broad by Joe Obbish, Erik Darling, RDFozz, hot2use, joanolo Jul 28 '17 at 17:21

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • The key thing you need to minimise in Azure SQL Data Warehouse is data movement. Please post some sample ddl ((whether it's distributed by round robin or hash, partitioning) and rowcounts for the two tables – wBob Jul 29 '17 at 9:35

You should add a nonclustered columnstore index on the recommended columns. This will be compressed and should also provide a performance improvement.

  • How would that differ from the columnstores already on those columns? – Neil P Jul 31 '17 at 7:59

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