Fotia Ltd

Fotia Ltd

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