How to Create a Table with a Partitioned Clustered Columnstore Index


If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here’s a creation T-SQL example:

The partition scheme name goes on the outside of the parenthesis, the index name goes on the inside, and the order of the whole thing is a little counter-intuitive.

That’s it. That’s the entire post. I just had to do that this morning and had a tough time finding an answer online, so figured I’d blog it. There you go.

Previous Post
Free Webcast Wednesday: Pushing the Envelope with Indexing for Edge Case Performance
Next Post
Unindexed Foreign Keys Can Make Performance Worse.

8 Comments. Leave new

  • Short and sweet. Thanks!

  • Thanks for the post.
    I noticed that SalesDate is DateTime. But, the column name implies it is a date level information.
    My question: Is DateTime data type appropriate for date range type partitions?
    Maybe we should insist on the Date type only.

    • Joel – that’s outside the scope of this post, but that’s a great question for SQLhelp. Here’s how it works:

    • why should you not partition by a datetime (or only by dates)?

      Or if you assume that you can only partition by dates: would you have to split it on day basis or is it possible to split at month or year basis too? Since nearly everyone would agree with the latter (of course it depend on your data, how you should split). It makes clear, that you the split is always on a fixed value and it does not matter, how many values are between two ranges.

      BTW: you can split not only using INT columns but DECIMAL columns too (the difference is the same as for DATE vs. DATETIME).

      PS: I just wondered, why Brent used DATETIME and not DATETIME2 (DATETIME is not exact, so more a FLOAT / REAL than a DECIMAL and should not longer been used for new projects – see

  • Paul-Sebastian Manole
    September 16, 2020 11:42 pm

    What’s the deal with partitioning tables and indexes, Brent?
    When is it worth doing?
    Can you tell us the Pros and Cons?
    And how does it relate to file groups, disk spanning and RAID?
    How much of a performance increase can one expect from partitioning?
    What’s the maintenance burden after partitioning?
    Do I have to change my backup plans?
    Thank you!

  • Little hint:
    If you use SSDT, then you have to repeat the partition-scheme definition for the index
    e.g.: INDEX MyIndexName CLUSTERED COLUMNSTORE ON ps_MyPartitionFunction(SalesDate)
    Otherwise SSDT build will fail. Got this in one of my projects today.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.