Free Women Wetting Themselves

Optimizing queries using partitioned views

Hello everyone!

partitioned views, like the shared tables in SQL Server 2005, are a simple way to distribute data on multiple FILEGROUPS to reduce maintenance costs. It is even possible with distributed partitioned views distribute the load, simply by storing different tables on different servers. Partitioned views
therefore include data from multiple tables whose structure is identical. Thus, all operations of insertion, modification and deletion can be performed on a partitioned view, subject to a condition sine qua non: the key to divide must be part of the primary key of each table aggregated. If

partitioned views are very commonly used to aggregate the data within data centers multisite, partitioned views are nonetheless the only way to horizontal partitioning on SQL Server 2000.
Therefore, these views prove to be unavoidable under SQL Server 2000 to architect a solution for high volume or have a sliding window of data - in this particular case, the key is commonly the date.

To better understand how to optimize a partitioned view, start with an example. Create two tables with the same structure.
CREATE TABLE TB1 (Id INT NOT NULL PRIMARY KEY CONSTRAINT PK_TB1, PartitionCol INT NOT NULL) CREATE TABLE
TB2 (Id INT NOT NULL PRIMARY KEY CONSTRAINT PK_TB2, PartitionCol INT NOT NULL)

Sight Next is the view then "partitioned" which allows to concatenate the data from these two tables: CREATE VIEW
VALL AS SELECT Id FROM TB1 PartitionCol UNION SELECT Id FROM TB2 PartitionCol

However, this view is far from For best performance, performing the same task of recognition in order to avoid duplication.
Suppose now that our data are distributed so distinct as the key. Then we can safely write:
VALL ALTER VIEW AS SELECT Id FROM TB1 PartitionCol UNION ALL SELECT Id FROM TB2 PartitionCol

This view is now significantly better, but still not very interesting.
now apply CHECK constraints type system to ensure the proper separation of data, such as separating the negative positive elements:
ALTER TABLE ADD CONSTRAINT TB1 CK_TB1_PartitionCol CHECK (PartitionCol < 0)
ALTER TABLE ADD CONSTRAINT CK_TB2_PartitionCol TB2 CHECK (PartitionCol> = 0)

By leveraging our view using the following command, we obtain finally the expected result:
SELECT Id, PartitionCol VALL FROM WHERE = 50 PartitionCol

Indeed, one can see the execution plan of our request that only the table TB2 is actually queried.

We can now proceed to the final step in optimizing our partitioned view. To do this we must add our allocation in the primary key. Thus, we can use our partitioned view to insert and modify our data.
ALTER TABLE DROP CONSTRAINT TB1 PK_TB1
ALTER TABLE ADD CONSTRAINT TB1 PK_TB1 PRIMARY KEY (Id, PartitionCol)
ALTER TABLE DROP CONSTRAINT TB2 PK_TB2
ALTER TABLE ADD CONSTRAINT TB2 PK_TB2 PRIMARY KEY (Id, PartitionCol)

command following is possible!
INSERT INTO VALL (Id, PartitionCol) VALUES (10, 1000)

I agree that these easily partitioned views have many difficulties in implementation, and that in matters of "sliding window", nothing beats partitioned tables in SQL Server 2005. However, it is an architecture in SQL Server 2000 or a distributed architecture across multiple servers, partitioned views prove to be a formidable weapon in the arsenal of Database Developer.

soon, and all your bench!