Partitioned tables, views

[redlam]

I am considering a solution using partitioned tables and local partitioned views. According to BOL, a set of partitioned tables must have check constraints on each primary key of each member table which allows a specific range of values. Inserts to the partitioned view must contain a primary key value that satisfies one of the member table constraints. Am I missing something here? It sounds like this solution will only work if you expect a finite number of records which can be split evenly across member tables upon creation of the table. As we are a software company, and distribute our product to different clients with different requirements, I have no way of predicting the number of records. If my assumption is correct, it sounds like this solution would not work in my case. Does anyone have any experience with partitioned tables and views? Can anyone shed any light?
Thanks in advance...

 

[bperry]

Hi there,
I don't know a whole lot about partitioned tables&views. But I was on a course last year that spent an hour or so on it - we did a lab to actually try it out - but I haven't used it since. But maybe I'll mention what I remember from that day, in case somehow it means anything to you. (Probably most of this you will already know.)

It seems to me that people get interested in this topic for two situations, both related to performance:
[ol]
[li] They have a large table (tons of rows) that must be kept together for historical reasons, but much of it is archival and only a small portion is needed for current operations.

Say a history of issue transactions from a small parts inventory. Only the most recent ones are of current interest, but sometimes they like to query the entire history too. But when running ordinary queries, the large number of rows in the table slows down processing for even current inquiries.

So the transactions are spread across separate tables, perhaps, say, by Year. or maybe Branch. Then we create views of these partitioned tables. These views are based somehow on the partitioning column (I'm trying to remember here), so that queries can figure out 'which' table(s) they need to go to to retrieve the data. Current inquiries run quicker now because the old historical data has been isolated to other tables.

The views of course keep all this hidden from the users. They are just Selecting on tblIssuesView, unaware that the data is physically split across separate tables. And when we decide to change the underlying partitioning, the views can hide that work, making it transparent to the users.

The partitioning column must be part of the Primary Key (doesn't have to be the whole PK, as I remember.) So in this example, maybe the PK is PartNum+IssueDate. Something like that.

[li]The above scenario can loosely be referred to as 'local partitioning'. But we can take it a step farther and have 'distributed partitioning'. Obviously, the idea here is to get more servers involved in the query: having multiple CPUs crunching the data can result in faster response time if the data splits (i.e. the partitioning) is done correctly. (As usual, there's a cost involve here, and we have to factor in network delays if we are retrieving large result sets from several other servers.)

Again, through the partitioned views, the client applications do not need to know where the data is located, or even what logic was used to split and partition the data.
[/ol]
---------------------------------
That's about all I remember. Don't know if it was worth passing all this on to you. It didn't really sound like this is the kind of thing you need for your situation (did it?)

Those course materials I mentioned are at my office, I could look at them at the first of the week to see if there's anything else worth mentioning.

Hope this helped a bit,
bperry

 

[redlam]

Thanks for all the info. I have since partitioned our table by year, similar to what you mentioned above. The only wall I've hit so far is that inserts are not allowed if there is an identity field in any of the base tables but I can probably get around this another way.
Thanks again!