Alter Database Objects to Support Partitioned Views
A partitioned view combines horizontally partitioned data from
across one or more servers (distributed partitioned views). A member table can be thought of as a segment from a larger table. There are two types of partitioned views in SQL Server: a
local partitioned view
. A local partitioned view is a partitioned view where all member tables reside on the local instance of SQL Server. Distributed partitioned views are new to SQL Server 2000 and are a bit more advanced than local partitioned views. The key difference between a distributed partitioned view and a local partitioned view is that in a local partitioned view, the data is collected from a single server alone. In contrast, a distributed partitioned view collects data from two or more instances of SQL Server, hence the
Figure 7.15. An illustration of a how a distributed partitioned view works.
With a distributed partitioned view, the data resides on several servers that work together to maintain and display the data. These servers may be called
federated database servers
In simple words, with partitioned views, tables that store large amounts of data can be split up (using horizontal partitioning) into smaller member tables. This data in the member table holds the same number of
Defining Union All UNION ALL specifies that multiple queries are to be combined into a single resultset. The ALL argument specifies that all rows be incorporated, even duplicate value rows. If ALL isn't specified, duplicate values are removed.
Partitioned View Considerations and Guidelines
When creating partitioned views, you should give careful thought to a few considerations:
To decide whether you should create a partitioned view or not, you have to examine the data you will be working with, and consider how it is used. If you have a table that is used by many different department or
For example, a company sells five major product lines. Each product line is managed by a department and each department has its own SQL Server for its specific data. All customers in the organization buy products from only one department, and there is no crossover between product lines, but all customers are stored in one table, which is stored on a central server. When looking for its own customers, each department must then SELECT against the central table, sifting through the entire customer base. Thought has been given to splitting the data into separate customer tables, but the central billing application requires that all the data be stored in one table.
In this scenario, you have an ideal candidate for a distributed (updateable) partitioned view. The customer table can be divided into tables based on product line, and joined together by a partitioned view. This enables each table to be queried individually or through the view. The
constraint can be based on the product line that the customer purchases, enforcing which server is to hold the data. Even if the view is queried, the
constraint is used to determine which servers and tables actually have to be queried. If the
against the view uses a
clause to specify
, then only the servers that contain those products are queried. This reduces the volume of data that is actually queried against. In this scenario, the central billing application can make use of the partitioned view, and it appears that all the data still resides in one table. Now you have enough knowledge of partitioned views to actually implement them, which you will do in the
Creating Partitioned Views
After reviewing all the guidelines for creating partitioned views, you are ready to implement them in SQL Server. When creating partitioned views, as noted earlier, the first step is cutting the table into horizontal sections, each section being called a member table and having the same number of columns and same attributes as the original table. To create a partitioned view, follow Step by Step 7.8.
Using WHERE with Your Partitioned View When you query a distributed partitioned view based on the partitioning criteria in the WHERE clause, you are querying against only the servers that fall within the scope of the WHERE clause. This yields a performance increase for you because of the way the distributed partitioned view uses the mandatory CHECK constraint. You receive the same advantage if you use CHECK constraints on local partitioned views.
If you perform a SELECT against this view, you will find that it returns the entire resultset. This complete resultset is achieved by selecting against each individual table.
With the completion of this section, you should be able to explain what a partitioned view is and differentiate between local partitioned views and distributed partitioned views. You should also be aware that the UNION ALL operator is used to consolidate the different tables into a single view. Lastly, you should know that the table structure for each table used in the view must be identical. If you feel comfortable with this information, then move on to the next section, which covers application security through views and ownership chains.