The SQL Server query processor supports queries that rely on partitioned views. Partitioned views allow you to create multiple tables with constraints (essentially one table for each partition) and have the tables logically reunited in response to queries. Here is an example:
CREATE table Sales96Q1 constraint “Month between 1 and 3”
CREATE table Sales96Q2 constraint “Month between 4 and 6”
CREATE view Sales96 as
SELECT * from Sales96Q1 union all
SELECT * from Sales96Q2 union all
This data definition language (DDL) creates four tables, one for each quarter of sales, each with an appropriate constraint. The DDL then creates a view that reunites all four tables. Programmers must be aware of the partitioning for updates, but for decision support queries the partitioning is transparent. When the query processor receives a query against the view Sales96, it automatically identifies and removes tables that do not fall within the constraints of the query.
SELECT *
FROM Sales96 -- remember, this view has four tables
WHERE s_date between ‘6/21/1996’ and ‘9/21/1996’
If you issue this query, the query processor generates a plan that touches only two of the tables in the view (Sales96Q2 and Sales96Q3), because the WHERE clause makes the other two tables irrelevant to the query. Different access paths can be used for the individual quarters. For example, you can use an index scan for the few days in Q2 (6/21-6/30) and a table scan for Q3. This is a useful method of improving the performance of queries that tend to select subsets of large tables on a well-known column. Time and location are typical examples.
The query processor detects all empty results when a constraint contradicts the selection criteria, even if you have not declared a view.