Alter Database Objects to Support Partitioned Views


  • Alter database objects to support partitioned views.

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.

graphics/07fig15.gif

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 Guidelines

When creating partitioned views, you should give careful thought to a few considerations:

  • Local partitioned views do not need to use CHECK constraints. Not using CHECK constraints also provides the same results as using a CHECK constraint, except that the Query Optimizer has to perform a lengthy search against all member tables meeting the query search condition. Using CHECK constraints reduces the cost of queries.

  • When creating partitioned views, be sure that all columns of each member table are included in the partitioned view definition. Also, make sure that the same column is not referenced twice in the SELECT list. Make sure that all identical columns in all tables are of the same data type.

  • When referencing member tables in a view, be sure to use the FROM clause to specifically declare a reference each table will use.

  • Be sure that Primary Keys are defined on the same column for each member table.

  • It is possible to create updateable partitioned views. This requires that each table's SELECT statement refers to only one base table, the UNION ALL operator is used to join the resultsets together, and non-local tables use the full four-part identifier in their names .

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 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.

STEP BY STEP

7.8 Creating a Partitioned View

  1. Initiate the SQL Query Analyzer by selecting Query Analyzer from the Start menu.

  2. You need the member tables to exist before you can gather the partitioned data. For this example, you will create three member tables to hold products for a multi-national food company. This company makes products to be sold in several different countries , and the products have been separated into tables related to their regions or countries. The following code creates the three tables:

    [View full width]
     
    [View full width]
    CREATE TABLE Product1 ( Product_ID INT PRIMARY KEY CHECK (Product_ID BETWEEN 1 and graphics/ccc.gif 50), Product CHAR(30) ) CREATE TABLE Product2 ( Product_ID INT PRIMARY KEY CHECK (Product_ID BETWEEN 51 and graphics/ccc.gif 100), Product CHAR(30) ) CREATE TABLE Product3 ( Product_ID INT PRIMARY KEY CHECK (Product_ID BETWEEN 101 and graphics/ccc.gif 150), Product CHAR(30) )
  3. These tables have to have some data before you can combine them into a view. INSERT two records for each as follows and as shown in Figure 7.16.

     INSERT Product1   (Product_ID, Product)   VALUES ('1', 'Zcheese') INSERT Product1   (Product_ID, Product)   VALUES ('5', 'AustralianJam') INSERT Product2   (Product_ID, Product)   VALUES ('60', 'SpicyDelights') INSERT Product2   (Product_ID, Product)   VALUES ('69', 'FarEastSpecialty') INSERT Product3   (Product_ID, Product)   VALUES ('120', 'DelicateClam') INSERT Product3   (Product_ID, Product)   VALUES ('140', 'FishNChips') 
    Figure 7.16. Inserting sample data into the member tables.

    graphics/07fig16.jpg

  4. Now, to create a partitioned view that collects all this information, execute the following code as shown in Figure 7.16.

     CREATE VIEW AllProducts AS   SELECT *     FROM Product1     UNION ALL   SELECT *     FROM Product2     UNION ALL   SELECT *     FROM Product3 

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.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net