"Automatic" partitioning

Question

"Automatic" partitioning

Craig Smith

SSChasing Mays

Points: 600
More actions
June 17, 2015 at 6:29 pm

#305139

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
Of course you would have to manage it so you don't run out of partitions, but that's trivial. And they would all have to be on the same file group, but for the date scenario I'm thinking of that's fine, you are only loading one partition at a time..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

craig.g.smith (6/17/2015)
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
Of course you would have to manage it so you don't run out of partitions, but that's trivial. And they would all have to be on the same file group, but for the date scenario I'm thinking of that's fine, you are only loading one partition at a time..

I don't know the details of exactly how they work, but isn't this what a "partition function" is for?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Craig Smith SSChasing Mays Points: 600 More actions · Answer 2

sgmunson (6/18/2015)I don't know the details of exactly how they work, but isn't this what a "partition function" is for?

Yes, except as far as I can see you must define each partition range with a hard-coded constant value. I think Oracle has an option where you don't need to do that.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

craig.g.smith (6/17/2015)
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
Of course you would have to manage it so you don't run out of partitions, but that's trivial. And they would all have to be on the same file group, but for the date scenario I'm thinking of that's fine, you are only loading one partition at a time..

My partitioning is auto-magic each month. All you have to do is write a relatively simple proc to do it. Yes, it would be nice if it were a built in feature but I've also found out that such clever built in features frequently miss some of the very important stuff such as compacting all the free space out of the partition before making it Read-Only.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Craig Smith SSChasing Mays Points: 600 More actions · Answer 4

Jeff Moden (6/18/2015)My partitioning is auto-magic each month. All you have to do is write a relatively simple proc to do it.

That's what I'm trying to do but so far an exercise in frustration. I just realized I forgot to re-initialize my variable each iteration and that solved the problem.

I still haven't been able to figure out how to query the existing dates and pass them to the partition function command to create them all up front. I have something that I thought would work but I still get a type conversion error. But doing them one at a time in a loop is fine, and I had to create that code for the weekly loads anyway.