Database Reference
In-Depth Information
Figure 15-2.
Data layout with a partitioned view approach
As you can see, different tables can be placed into different filegroups, which can even be marked as read-only if
needed. Each table can have its own set of indexes and maintain individual, more accurate statistics. Moreover, each
table can have its own schema. This is beneficial if operational activities require tables to have additional columns,
for data processing, for example, which you can drop afterwards. The difference in schemas can be abstracted on the
partitioned view level.
It is extremely important to have
CHECK
constraints defined in each table. Those constraints help SQL Server
avoid accessing unnecessary tables while querying the data. Listing 15-5 shows an example of queries against a
partitioned view.
Listing 15-5.
Queries against partitioned view
select count(*) from dbo.Orders;
select count(*) from dbo.Orders where OrderDate = '2014-06-03'
As you can see in Figure
15-3
, the first query requires SQL Server to access all of the tables from the partitioned
view. Alternatively, the second query has
OrderDate
as a parameter, which allows SQL Server to pinpoint the single
table that needs to be queried.
