Sorry for the interuption but I have to make sure that people understand a couple of things...
I'd like to warn everyone that reads this article or any other article on "how easy partitioning is" that it's not actually an easy thing to do except on totally static data (per underlying partitioned table) where the PK of the table is NOT used as an FK anywhere. As touched on but seriously underplayed in this article, if FK's point to the PK of this table, you'll need to make some major changes and you'll also need to understand that the partitioning column MUST be included in the PK. On partitioned tables, the partitioning column MUST be included in ANY and ALL unique indexes, as well.
What does that mean and why am I so concerned about all of this? Stop and think about it... if you add the stereotypical date or region column as the partitioning column, it must be added to the PK and, although the now 2 column PK can still be unique, the actual column that you were counting on being unique is no longer unique in and of itself. This can really raise hell with the PK and other columns that you actually want to contain unique values throughout the table.
You also can't make the partitioning column of partitioned views an IDENTITY column (you can in partitioned tables but that's a whole 'nuther set of problems). And, as also previously pointed out, you must insert ALL columns of data when doing an INSERT to partitioned views which pretty much leaves out the ability to make insertable partitioned views if an IDENTITY column is present. That means that you have to use a "staging table" if you intend to use an IDENTITY column and that means that you either need to make an INSTEAD OF trigger on the view to process inserts or you need to change all the software that points at the view (which used to be a monolithic table) to now insert to the underlying table (almost MUST be a staging table if an IDENTITY column is involved) and read from the view.
Don't get me wrong... partitioning of really large tables (especially temporal tables) provides huge advantages for backups, index/stats maintenance, and PieceMeal restores, but partitioning an existing table (even if it doesn't have an IDENTITY column) either as a partitioned view or a partitioned table is not a trivial task and careful planning is required or you're going to break a lot of data and/or break a lot of applications. As also previously suggested, you really should build a parallel system to experiment with and to make sure you understand all of the things that can go wrong and the things that you need to do to keep them from going wrong before the words "partition production tables" ever leaves your lips.
Please... carefully plan any partitioning you might do and make sure that you understand ALL the nasty little caveats that are inconveniently spread out all over hell's little half acre in Books Online. Then test the partitioning on a separate system until you're absolutely sick of testing and then test some more.
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)