Azure data warehouse supports both clustered and non-clustered indexes in addition to columnstore (which is the default for any new table).

I know that having a large clustering key is normally discouraged, as it forces sql server to internally generate a larger key to guarantee uniqueness (and unique constraints are not supported). Of course there are loads of benefits of using columnstores, such as batch mode and segment elimination, however columnstore will use hash match and hash join operations for analytical queries.

Experimentally I've seen situations where a clustered key performs better where all the grouping columns are included in the clustering key (4-5 columns, some varchars), because the guaranteed order allows a stream aggregate to be used instead.

My tables are distribution aligned and the estimated execution plan shows that the queries against the columnstore indexes are indeed using batch mode.

What are the potential pitfalls with this approach, is this scalable for billions of rows of data?

  • How large a table did you use in your experiment in both row count and size? Small, narrow tables are likely to work better with CI vs CCI. As you grow, this will flip. Billion row tables on non-CCI is usually not a good idea. Also, CI does not provide order guarantee, ever so please don't assume it will be even if you get it that way in 10000 query executions. Even with no DMLs, parallel operations can retrieve and union the data out of order. Without an ORDER BY, you have no guarantee, CI or no CI. – SQLmojoe Apr 11 '18 at 19:51
  • @SQLmojoe I'm a little confused, why would ordered = false for a CI? Or does disk fragmentation cause the ordered flag to change? – Neil P May 18 '18 at 11:10
  • 1
    It's written down in order initially but unless the data is static, the order is not maintained. More importantly, when you read the data, with intra-query parallelism, IO parallelism and SQL DW's MPP architecture, odds of not getting the same order is quite a bit higher than regular SQL Server (SMP) where order is also not maintained nor guaranteed on retrieval. – SQLmojoe Aug 1 '18 at 22:04

Your Answer

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

Browse other questions tagged or ask your own question.