A partitioned view combines horizontally partitioned data from member tables 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 and a distributed 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 name "distributed." Distributed partitioned views are used when processing needs to be spread throughout a group of servers, as shown in Figure 7.15. Figure 7.15. An illustration of a how a distributed partitioned view works.
NOTE Server Federations 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 . This term is used to refer to these servers that share the data-hosting job, but are independently administered. 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 columns as the original table; it is only the number of rows that is decreased. After the data is broken down into smaller member tables, a view defined with UNION ALL is used to bring all member tables together. This view looks like a single large resultset. When a SELECT query is run against the partitioned view, SQL Server uses CHECK constraints in determining which member table the data is from. The CHECK constraint is usually created on the Primary Key column. NOTE 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 GuidelinesWhen 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 regions (each with its own server), then you can look at partitioning the database along those lines. 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 CHECK 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 CHECK constraint is used to determine which servers and tables actually have to be queried. If the SELECT against the view uses a WHERE clause to specify product1 and product2 , 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 next section. Creating Partitioned ViewsAfter 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.
NOTE 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. |