SQL Server 2000 Partitioned Views

Partitioned views are a feature in SQL Server 2000 that allows an additional level of abstraction to be introduced between the orthogonal view of the data and its storage in an underlying table. In this article we explore what a partitioned view is, how it is created and accessed and, most importantly, what advantages it brings to the table.

• Introducing Partitioned Views
• Creating Partitioned Views
• Working with Partitioned Views
• Practical Uses of Partitioned Views
  • Traditional Partitioning
  • Large Skewed Tables
  • Coarse Indexing
• Conclusions

Introducing Partitioned Views

With most enterprise-level database applications, a point is reached where the amount of data residing in individual tables starts to become increasingly difficult to manage due to the ever increasing number of rows in these tables. Typically these are systems that have been running fine for a long period of time. One day somebody notices that backup times have steadily increased from 10 minutes to 2 hours. Queries that used to be sub-second now start to take 4 or 5 seconds and the accuracy of statistics on indexes has started to become an issue. The database has grown up and has developed new requirements that must be taken care of.

What would be great is if we could split-up those behemoth tables into a number of smaller chunks, and manage those small chunks independently. For example, in some of these tables we put old data that needs to be present but never changes. The other tables, we fill up with new data. As the new data becomes old, we move it over to the old-side, thereby keeping the new data relatively small and fresh.

This is a great idea except the database design is now being influenced by factors other than the functional requirements of the application. What we'd really like to do is abstract away the segregation of data amongst constituent tables but still present a unified virtual table to our TSQL that behaved as though it was just a single table. This is precisely what partitioned views were designed to do.

A partitioned view is a database object that allows data, that has been segregated into a number of discrete tables, to be viewed as a single, orthogonal and contiguous result set (i.e. a virtual table). In the same manner as views allow an arbitrary TSQL SELECT statement to be be queried as a virtual table, partitioned views allow the caller to interact with the view without any knowledge of the underlying tables. However, the main difference between normal views and partitioned views is that partitioned views exhibit all of the same properties and behaviours as an actual table. If all of the rules are followed, partitioned views can be INSERT'd, DELETE'd and UPDATE'd as though they were actual tables. SQL Server will take care of figuring out how the action being performed to the view translates to the data partitioning between tables.

Creating Partitioned Views

The first step in creating a partitioned view is to determine what the underlying tables look like and how the data will be partitioned (i.e. segmented) between the tables. In this demonstration we have two tables: TablePos and TableNeg. You'll notice that the structure of the base tables is identical. The only difference between them is the check constraint on the partition column. For TablePos I've specified that the value of partition has to be positive. For TableNeg it must be negative. We'll see later how this difference is at the core of partitioned view features:

For a real-world implementation of a partitioned view the partitioning column can be a date (e.g. all orders in year 2004, 2004, etc.) or a customer's surname (A-F, G-L, M-R, S-Z). The segmentation of the partitioning column range is totally up to you. The only restriction is that it there must not be any overlap between the partitioning column ranges. Gaps are allowed, overlap is not. Now that we have our base tables we create a view over the partitions.

I've used the SCHEMABINDING option in the create view. This will prevent any of the underlying tables from being altered without the view being dropped. This is not necessary for partitioned views but it helps reinforce the restriction that no changes can be made to the base tables without thinking about the consequences for the partitioned view. All access to the base tables will be through the partitioned view, including INSERTs, UPDATEs and DELETEs. This is the power of partitioned views. It abstracts away the data partitioning from the view of the data as a single entity. In addition, the partitioned view exposes features that are not possible by accessing the base tables directly, we'll see those features next.

An interesting characteristic of partitioned views is that the base tables don't have to reside in the same database or even on the same SQL Server. Using linked servers you can have a partitioned view that accesses base tables that are distributed amongst a number of SQL Servers. This is known as a distributed partitioned view. When a user accesses this partitioned viewed and SQL Server decides that the data the caller is querying resides on the other server, SQL Server will go and intelligently fetch that data from the remote SQL Server and return it to the client. For example, the following TSQL creates a distributed partitioned view:

In this case, the partitioned view needs to be created on each SQL Server that client will connect to and access the partitioned view. In this case we are creating the view on SQLServerA and SQLServerB. For the sake of this article I'll just use a local partitioned view but all of the following features apply equally to distributed partitioned views.

Next >>>