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:
|
1 2 3 4 5 6 |
CREATE TABLE dbo.MyTableName( Id int IDENTITY(1,1), SalesDate datetime NOT NULL, INDEX MyIndexName CLUSTERED COLUMNSTORE ) ON ps_MyPartitionFunction(SalesDate); GO |
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.
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: https://brentozar.com/go/sqlhelp
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 https://docs.microsoft.com/de-de/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15)
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!
Paul – use the search function in this site and we’ve got tons of posts covering that. Thanks!
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.
Thanks for your comment, I got this today myself and couldn’t figure it out. Legend