Professional SQL Server™ 2005 Programming

9.10. Partitioned Views

These have been in use just since SQL Server 2000, but they are already being deprecated by Microsoft. I bring them up here because they were one of the leading scalability options put forth by Microsoft for many years, and you need to know how they work in case you run into them in legacy code. With SQL Server 2005, they are being replaced by partitioned tables.

A partitioned view is a view that unifies multiple identical (in terms of structure — not actual data) tables and makes them appear to be a single table. At first, this seems like an easy thing to do with simple UNION clauses, but the concept actually becomes somewhat tricky when you go tohandle insert and update scenarios.

With partitioned views, we define a constraint on one of the tables in our view. We then define a similar, but mutually exclusive, constraint on a second (and possibly many more) table. When you build the view that unifies these mutually exclusive tables, SQL Server is able to sort out the exclusive nature of the tables in a logical manner. By doing this, SQL Server can determine exactly which table is to get the new data (by determining which table can accept the data — if you created them as mutually exclusive as you should have, then the data will be able to get into only one table and there is no conflict).