Practical Uses of Partitioned Views (continued...)
Coarse Indexing
Another alternative way to look at a partitioned view is to see it as a replacement
for a coarse index. If you split up the contents of a single table into a number
of partitions and each of those partitions has a constant for the check constraint
describing each partition, then you no longer need to index that column. This may
sound a bit strange but it is immensely useful when you come across an opportunity
to use it.
Consider a table that has clustered key that is forced to use a column that has
poor cardinality. For example, the clustered index may include the isDeleted flag
or a state column. In both cases almost every query will include a value for the
column in its WHERE clause (i.e. you only want to consider rows that haven't been
logically deleted or you are only interested in rows that are in a specific state).
You're therefore forced to index this column as every query includes a value for
it but its poor cardinality makes it a bad candidate. Partitioned views can really
help out here.
If you make the partitioning key the column with poor cardinality then you no longer
have an indexing problem! If you fix the check constraint for each underlying table
to a constant (e.g., TableDeleted has isDeleted = 'Y', TableNotDeleted has isDeleted
= 'N', etc.) then the portion of the WHERE clause that references the partitioning
column no longer needs the index. Instead it just resolves the value of the partitioning
column used to an underlying table! In addition, as we saw earlier, if you update
the partitioning column SQL Server will move the row to the correct underlying table
as long as the update is performed through the partitioning view.
Conclusions
Hopefully this article has given you a good grounding in partitioned views and shown
you some of the standard and more imaginative applications of this very useful technology.
<<< Previous