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?

  • 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.