Distributed Partitioned Views balances load by partitioning a database among multiple machines and sending queries and updates to the machine that holds the affected data. The SQL 2000 query processor uses views (virtual tables) to locate the data, hence the term "Distributed Partitioned Views."

The diagram illustrates how this works in a simplified order entry application (similar to the one used in the TPC-C benchmark). An incoming orders table is split across two machines. Each machine has a partial table that holds incoming orders for a specific group of warehouses (based on the warehouse name). Each machine also has an order view that merges the partial tables, creating a virtual table with incoming orders from all warehouses.

To enter a new order for the New York warehouse, the application goes to either of the two machines and inserts a row into the order view (1). On receiving the insert command, the SQL Server query processor uses the view definition to find the machine that includes New York and forwards an insert command to that machine (2). That machine executes the command, inserting the order into its table.

Distributed Partitioned Views can yield high scalability, but the scheme also has high administrative and development costs in SQL 2000 EE. The database administrator has to set up views and partial tables on all machines; each machine gets a slightly different schema and different data. Adding an additional machine requires the administrator to update the schemas and data on all machines. Furthermore, Microsoft's TPC-C benchmark application got its stellar numbers in part by performing "data-directed routing"—sending operations on a warehouse directly to the machine that manages the warehouse and bypassing the forwarding in step (2) above. SQL 2000 EE customers will have to add routing code to their applications to get similar payoffs.

Back to associated article: SQL Server 2000 Steers Toward Enterprise