SQL Server's partitioned views let organizations tune their systems to handle a lot of data. This feature lets you partition data across several tables; logically uniting the tables via a view makes the partitioning as transparent as possible to users and developers. Several previous articles (including two by Kalen Delaney and me, "Distributed Partitioned Views," August 2000, InstantDoc ID 9086, and "Querying Distributed Partitioned Views," September 2000, InstantDoc ID 9097) covered partitioned views. SQL Server Books Online (BOL) also covers the foundations. In this article, I share a few tips that you might not be aware of, including the negative effect of using Enterprise Manager to make schema changes to partitions, how to circumvent the limitation on how you can insert data into the tables through the partitioned view, and problems caused by using constants in the partitioning criteria.
Altering Partitions from Enterprise Manager
I'm an avid advocate of using T-SQL even for tasks that you can perform easily through Enterprise Manager. Besides improving my T-SQL skills, this approach lets me better understand what I'm doing and have more control over it. In addition, Enterprise Manager doesn't always perform a task the most efficient way. Making schema changes to partitions from Enterprise Manager, for example, can degrade partitioning performance and functionality. To walk through this problem, first run the script in Listing 1 to create the partitions Orders2000, Orders2001, and Orders2002 and the updateable partitioned view Orders.
First, you need to make sure that Orders provides full partitioning functionality in terms of updateability and partition elimination during retrieval. Partition elimination means that SQL Server accesses only the relevant partitions for your query's filter criteria. Populate Orders with the following three orders, each of which goes to a different partition because each partition hosts rows from a different year:
INSERT INTO Orders(orderid, customerid, orderdate)
VALUES(1, 'aaaa', '20000109')
INSERT INTO Orders(orderid, customerid, orderdate)
VALUES(2, 'aaaa', '20010118')
INSERT INTO Orders(orderid, customerid, orderdate)
VALUES(3, 'aaaa', '20020212')
The Orders partitioned view meets all the requirements for updateability as described in BOL, so the INSERT statements are successful. Now, to verify that partition elimination is occurring for data retrieval, turn on STATISTICS IO and, from the Query menu in Query Analyzer, turn on the Show Execution Plan option. Note that by turning on this option, you request the actual plan that the optimizer generates and uses when you run a query (as opposed to the Display Estimated Execution Plan option, which shows an estimated plan without running the query).
To test partition elimination, issue the following SELECT statement against Orders to retrieve data only for January 2000:
SET STATISTICS IO ON
SELECT * FROM Orders
WHERE orderdate >= '20000101'
AND orderdate < '20000201'
In the STATISTICS IO result, note that SQL Server physically accessed only Orders2000; the amount of I/O against all other partitions is 0:
Table 'Orders2002'. Scan count 0, logical reads 0, ...
Table 'Orders2001'. Scan count 0, logical reads 0, ...
Table 'Orders2000'. Scan count 1, logical reads 2, ...
In the graphical execution plan, hover your mouse pointer over the three clustered index seek operators. Notice in the yellow details boxes that only the branch in the plan for the Orders2000 partition actually executed (Number of Executes: 1); the others didn't (Number of Executes: 0).
Now, suppose you have to enlarge the customerid column from varchar(5) to varchar(10). Using T-SQL, you can write a short, efficient script that runs in a split second because SQL Server doesn't need to physically access every row to enlarge a varchar column; it just expresses the change in the metadata. Running the following code performs the change:
ALTER TABLE Orders2000
ALTER COLUMN customerid VARCHAR(10) NOT NULL
ALTER TABLE Orders2001
ALTER COLUMN customerid VARCHAR(10) NOT NULL
ALTER TABLE Orders2002
ALTER COLUMN customerid VARCHAR(10) NOT NULL
Try retrieving data after running this script, then rerun the query against Orders and examine the plan and the results of STATISTICS IO as you did before:
SELECT * FROM Orders
WHERE orderdate >= '20000101'
AND orderdate < '20000201'
Notice in the results that partition elimination is taking place. Issuing an INSERT query is also successful:
INSERT INTO Orders(orderid, customerid, orderdate)
VALUES(4, 'aaaa', '20020828')
To see how Enterprise Manager handles the change, click the Tables folder under the tempdb database where the partitions were created, right-click the Orders2000 table, and select Design Table. In the Design Table dialog box, change the customerid column size to varchar(15). Repeat this process for the Orders2001 and Orders2002 partitions. Click Save Change Script before saving your changes in one of the partitions, and examine the script SQL Server generates. You should get the (abbreviated) script that Listing 2 shows for Orders2002.