Distributed Partitioned Views

Now that you're aware of the requirements for the tables, you can create the tables. In this example, we show you how to create tables that are similar in structure to the Customers and Orders tables in the Northwind database. We'll distribute the rows among the tables based on the first letter of the customer ID: A through F will go in the first table, G through P in the second table, and Q through Z in the third table. The following code samples run in a database called testdb, which we created on each of the servers. Listing 4 shows the script that creates the first partitioned tables in the server Shire\Shiloh (Node1).

Notice a few important points in the script. We have a CHECK constraint on the customerid column, which is part of the primary key in the CustomersAF and OrdersAF tables. This CHECK constraint ensures that only customers with customer IDs in the range AAAAA through FZZZZ can be in this table, and the optimizer will use this CHECK constraint to optimize queries and modify data. If you want to partition the Orders table by customerid, you need to include the customerid column in the primary key. Partitioning the original Customers table by the customerid column is obvious, but why do we use the same partitioning criterion for the Orders table? We'll explain the partitioning criterion in an upcoming article when we demonstrate how to query the partitioned view. Note that for the purpose of guaranteeing uniqueness of rows, it isn't necessary for the customerid column to be part of the primary key. However, to effectively maintain the partitioned data, customerid needs to be part of the key of the Orders table so that you can partition the Orders table by the customerid column.

Next, create the partitioned tables in the Hobbiton \Shiloh (Node2) server, with customer IDs in the range GAAAA through PZZZZ, as Listing 5, page 36, shows. And last, create the partitioned tables in the Rivendell \Shiloh (Node3) server, with customer IDs in the range QAAAA through ZZZZZ, as Listing 6, page 37, shows.

Creating the partitioned views. Now that you've set up the partitioned tables, you need to assemble them, which is probably the easiest part of the process. You define a view that assembles the rows from each table by using the UNION ALL operator. In each server, you have one local table and two remote tables, so the view looks slightly different in each server. You reference the local table by using only the table name, and you reference the remote tables by using the four-part table name, such as Node2.testdb.dbo.CustomersGP.

Let's create a partitioned view in the Shire\Shiloh (Node1) server, as Listing 7 shows. After you create the view, users can start modifying and querying it if they're connected to Shire\Shiloh (Node1). Creating similar views on the other two servers enables the same modifications and queries, regardless of the server the users are connected to. Next, create similar views on Hobbiton\Shiloh (Node2), as Listing 8 shows. And last, create similar views on Rivendell\Shiloh (Node3), as Listing 9, page 38, shows.

As the partitioned tables do, the views need to meet a few conditions to be updateable and to exploit the new optimizing capabilities that distributed partitioned views feature:

• The view can't reference any table or column more than once.
• Each SELECT list must reference all the columns participating in the primary key of the underlying tables.
• The columns in the same ordinal position in the select list in all the select statements must be of exactly the same data type, precision, scale, and collation. And the partitioning column must be in the same ordinal position in all the select statements.
• If a column exists in the base table but doesn't exist in the select list in the view, the column must allow NULLs.

Note that the last two requirements are much stricter than any requirement for creating local, nonpartitioned views. Simple views must have compatible data types in the corresponding positions of the select lists, but the types don't have to be exactly the same. Also, you can insert data into simple views even if columns from the base table that aren't included in the view don't allow NULLs but have a defined default. But if you're inserting data into a distributed partitioned view, the columns can't have defaults.

Modifying Distributed Partitioned Views
When you create distributed partitioned views, you need to ensure that the underlying tables and the views created on them meet certain criteria. Most statements that you use to modify a distributed partitioned view also need to meet certain requirements. Delete statements have no restrictions; you can delete any or all rows from the view. However, insert and update statements need to meet the following requirements for you to use them with distributed partitioned views:

• An insert statement against the view must supply a value to the partitioning column.
• If you are running a beta version of SQL Server 2000, an update statement against the view might fail if an attempt is made to modify the primary key. You can issue a delete statement followed by an insert statement instead of an update statement. Note that Microsoft announced that the final version of SQL Server 2000 will support updates to the primary key.

In this example, we ran the following modifications from our connection to Shire\Shiloh (Node1), but you can run the modifications from a connection to any of the servers. Make sure the Microsoft Distributed Transaction Coordinator (MSDTC) service is running, because these modifications will result in a distributed transaction. You also need to set the XACT _ABORT session setting to ON. This setting determines whether an entire transaction will roll back if a runtime error occurs, but in this case, this setting is required for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. Make sure you turn on the Graphical Execution Plan in Query Analyzer to see the detailed execution plan, or use SET STATISTICS PROFILE ON if you prefer to analyze the execution plan in a textual mode.

Now you can populate the Customers view with all the rows from the Customers table in the Northwind database, as Listing 10, page 38, shows. Notice that we ran the insert statement against the view. Figure 2, page 38, shows the execution plan, which illustrates how SQL Server processed the insert statement. Let's analyze the execution plan's important steps (and ignore one trivial step):

• Step 1 (Clustered Index Scan): Read all of the rows from the local Customers table in the Northwind database.
• Step 2 (Compute Scalar): For each of the rows read in Step 1, compute a new column called Ptn1034, which will hold a value that specifies which of the three ranges of rows it belongs to—0 if the partitioning column is AAAAA through FZZZZ, 1 if the partitioning column is GAAAA through PZZZZ, and 2 if the partitioning column is QAAAA through ZZZZZ.
• Step 4: Store the rows read in Step 1, including the computed column Ptn1034, which was calculated in Step 2 in a temporary table.
• Steps 5 and 6: Read the temporary table and insert only the rows that match the criteria Ptn1034 = 0 into the local CustomersAF table.
• Steps 7 through 9: Read the temporary table and insert only the rows that match the criterion Ptn1034 = 1 into the remote Node2.testdb.dbo .CustomersGP table.
• Steps 10 through 12: Read the temporary table and insert only the rows that match the criterion Ptn1034 = 2 into the remote Node3.testdb.dbo .CustomersQZ table.

You can issue insert, update, and delete statements against the view. Try the modifications that Listing 11 shows against the view with a connection to Node1 and compare the execution plan for statements that modify a local table with the plan for statements that modify a remote table. You'll see that the optimizer splits and routes the modifications to the relevant nodes and can determine when to perform a local operation and when to perform a remote one.

Long Nights, Partitioned Views
Microsoft code-named the distributed partitioned view project Coyote, and we imagine that the Coyote team worked many long nights on this new feature. In the September issue, we'll demonstrate the next step in the process—querying distributed partitioned views. We'll show you the execution plans for various queries so you can see how SQL Server distributes queries across the nodes. And in a third article in an upcoming issue, we'll offer suggestions for achieving similar functionality when your tables or views don't meet the criteria for distributed partitioned views that we described here.