Views in SQL Server 2000, Part 5

Table of Contents
Introduction
Advantages of using views
Create a view
Create a simple view
With Encryption
With Schemabinding
With View_Metadata
With Check Option
Using views
Retrieve data
Update data
Drop views
Alter views
Partitioned views
Indexed views

Partitioned views

A partitioned view uses the UNION ALL operator to merge all member tables (which must be structured in the same way). The tables can be stored in the same SQL Server, or in multiple SQL Servers. It must however be clear what data belongs to each partition, by using CHECK constraints and data cannot overlap (so you cannot have one table with customers ID from 1 to 50, and another table with customer ID from 25 to 75). For example, say that we have three different kinds of customers, those from Sweden, those from UK, and those from USA. The number of rows in the customers table is increasing very fast, so we would like to split the existing table into three other tables, on three different servers (in my example, we will do this on one server only, so as many of you as possible can try it). We then create a view on each server, to access information about all customers. But, this is very smart, if we later use the view to access data from two servers (because the WHERE clause limits it), it will not transfer data from the third server.

Before creating the tables, it is a good idea to mention the rules for a so called partitioning column (the column(s) that makes it impossible to overlap data).

• It is not a calculated, identity, default or timestamp column
• It cannot have the value NULL
• It is part of the primary key
• It should be validated by a CHECK constraint, but cannot be validated using these operators <> and !
• Only one CHECK constraint exists on the partitioning column.

There are also some rules the tables need to follow:

• The primary key should be defined on the same columns
• The table cannot have indexes created on computed columns
• All tables should have the same ANSI padding setting.

Now, finally, let’s create the three tables (which we will in this example place in the same database (Northwind) on the same server).

SELECT * INTO CustomersSweden
FROM Customers
WHERE Country = 'Sweden'
GO
ALTER TABLE CustomersSweden ALTER COLUMN 
Country NVARCHAR(15) NOT NULL
GO
ALTER TABLE CustomersSweden ADD 
CONSTRAINT PK_CustomersSweden PRIMARY KEY (CustomerID, Country),
CONSTRAINT CK_CustSweden_Country CHECK (Country IN ('Sweden'))
GO

SELECT * INTO CustomersUK
FROM Customers
WHERE Country = 'UK'
GO
ALTER TABLE CustomersUK ALTER COLUMN
Country NVARCHAR(15) NOT NULL
GO
ALTER TABLE CustomersUK ADD
CONSTRAINT PK_CustomersUK PRIMARY KEY (CustomerID, Country),
CONSTRAINT CK_CustUK_Country CHECK (Country IN ('UK'))
GO

SELECT * INTO CustomersUSA
FROM Customers
WHERE Country = 'USA'
GO
ALTER TABLE CustomersUSA ALTER COLUMN
Country NVARCHAR(15) NOT NULL
GO
ALTER TABLE CustomersUSA ADD
CONSTRAINT PK_CustomersUSA PRIMARY KEY (CustomerID, Country),
CONSTRAINT CK_CustUSA_Country CHECK (Country IN ('USA'))
GO

This script creates three different tables (CustomersSweden, CustomersUK and CustomersUSA), all based on the Customers table in the Northwind database. But as you remember, a partitioning column cannot be NULL, so we have to alter the column Country so it does not accept NULL values. We also have to add a PRIMARY KEY constraint, and a check constraint.

After we have created the tables, we can create the partitioned view:

CREATE VIEW vwCustomers
AS
        SELECT * FROM CustomersSweden
        UNION ALL
        SELECT * FROM CustomersUK
        UNION ALL
        SELECT * FROM CustomersUSA
GO

If we now select all data from this view, it looks like it’s one table, with customers from USA, Sweden and UK.

SELECT * FROM vwCustomers

But, what happens if we insert data using this view?

INSERT INTO vwCustomers
VALUES 
(
        'SHOPS', 
        'Shop Shop Shop', 
        'John Doe',
        'Owner', 
        '111 East 12th', 
        'New York', 
        'NY', 
        '94117', 
        'USA', 
        '(415) 555-1234', 
        NULL
)

Yep, it’s John Doe again! Now try this, and see where you find John Doe.

SELECT * FROM CustomersSweden
SELECT * FROM CustomersUK
SELECT * FROM CustomersUSA

You also found him in the table CustomersUSA? And we do not even have to know that this table exists! He ends up there because that is the only table he will not violate the CHECK constraint in.

Say that John Doe moves to UK, but everything else is the same! (because we are lazy :-)

UPDATE vwCustomers
SET Country = 'UK'
WHERE CustomerID = 'SHOPS'

Note: Didn’t work? Only the Developer and Enterprise Edition of SQL Server 2000 allow INSERT, UPDATE and DELETE operations on partitioned views.

In which table do you think it’s stored this time? Yep, that is correct, in the CustomersUK table.

But, as always, there are some rules about updating data through a partitioned view.

• The INSERT statement must supply values for all columns in the view. It doesn’t matter if the underlying tables have a DEFAULT constraint, or allow NULLs.
• It must satisfy at least one of the underlying constraints (we cannot insert a customer from France in the example above).
• The DEFAULT keyword does not work in INSERT and UPDATE statements.
• Columns that are IDENTITY columns in one or more member tables cannot be modified.
• Data cannot be modified if one of the tables contains a timestamp column.
• If there is a self-join with the same view or with any of the member tables in the statement, INSERT, UPDATE and DELETE do not work.

Indexed views

It is beyond the scope of this article, but I thought it would be good to at least mention it. As you know now, a view is usually no more than a stored query. Using a view is not always fast, and for example when you must do some calculating based on a few millions rows, it can be very slow. An indexed view will however fix this, at least make it a little bit faster, because it will store the result set in the database, which is updated dynamically. The disadvantage of indexed views is that it will slow down a query that updates data in the underlying tables. You can find more information about indexed views in Books Online.

[ 1 ] [ 2 ] [ 3 ] [ 4 ] [ 5 ] [ Next ]

Home | Copyright © 2002 - 2005 Kristofer Gäfvert