Partitioned Views in SQL 2008

A partitioned view is a view that unifies multiple identical (in terms of structure — not actual data) tables and makes them appear to be a single table. At first, this seems like an easy thing to do with simple UNION clauses, but the concept actually becomes somewhat tricky when you go to handle insert and update scenarios.

With partitioned views, we define a constraint on one of the tables in our view. We then define a similar, but mutually exclusive, constraint on a second (and possibly many more) table. When you build the view that unifies these mutually exclusive tables, SQL Server is able to sort out the exclusive nature of the tables in a logical manner. By doing this, SQL Server can determine exactly which table is to get the new data (by determining which table can accept the data — if you created them as mutually exclusive as you should have, then the data will be able to get into only one table and there is no conflict). The only catch is that the so called ‘‘partitioning column’’ must participate in the primary key.

Here’s what a two-month set of data might look:

CREATE TABLE OrderPartitionJan08(

OrderID int NOT NULL,

OrderDate date NOT NULL

CONSTRAINT CKIsJanOrder CHECK (OrderDate >= ’2008/01/01’ AND OrderDate < ’2008/02/01’),

CustomerID int NOT NULL,

CONSTRAINT PKOrderIDOrderDateJan PRIMARY KEY (OrderID, OrderDate)

);

CREATE TABLE OrderPartitionFeb08(

OrderID int NOT NULL,

OrderDate date NOT NULL

CONSTRAINT CKIsFebOrder CHECK (OrderDate >= ’2008/02/01’ AND OrderDate < ’2008/03/01’),

CustomerID int NOT NULL,

CONSTRAINT PKOrderIDOrderDateFeb PRIMARY KEY (OrderID, OrderDate)

);

GO

CREATE VIEW Orders

AS

SELECT *

FROM OrderPartitionJan08

UNION ALL

SELECT *

FROM OrderPartitionFeb08;

Once we have created these tables along with the view that unites them into a partitioned view, we’re

ready to insert a few rows of data:

INSERT INTO Orders VALUES

(1, ’2008-01-15’, 1),

(2, ’2008-02-15’, 1);

Orders is a view, and therefore has no data of its own — so where does the data go? Under the covers,

SQL Server analyzes the data being inserted and figures out that, based on the constraints in our table, the data can, in each case, go to one and only one table. Let’s check that out with a few queries:

SELECT * FROM Orders;

SELECT * FROM OrderPartitionJan08;

SELECT * FROM OrderPartitionFeb08;

This gets us, in order, both rows we inserted, then the row from January, then the one from February.

OrderID OrderDate CustomerID

----------- ---------- -----------

1 2008-01-15 1

2 2008-02-15 1

(2 row(s) affected)

OrderID OrderDate CustomerID

----------- ---------- -----------

1 2008-01-15 1

(1 row(s) affected)

OrderID OrderDate CustomerID

----------- ---------- -----------

2 2008-02-15 1

(1 row(s) affected)

As you can see, our data has been split up into separate tables based on the partitioning column. We can easily create additional tables to partition our data into (for example, an OrderPartitionMar08 table) and then alter our view to union in the additional table. Likewise, we can easily remove a block of data by excluding it from the view and then dropping the table.

User-Defined Data Type (Table)

Creating a Table User-Defined Data Type

Creating a table user-defined data type works as something of a combination of the classic CREATE TYPE and table variable syntax. The tabular CREATE TYPE syntax looks like this:

CREATE TYPE [<schema name>.]<type name>

AS TABLE

(

{<column name><data type>

[ COLLATE <collation name>]

[ NULL | NOT NULL ]

[ DEFAULT <expression>]

[ IDENTITY [ ( <seed>, <increment>)]

[ ROWGUIDCOL ] [<column constraint> [ ...n ] ]

[<table constraint>]}

|<computed column definition>}

)[;]

Long ago, it seemed one address was generally enough for most people. The majority of systems out there stored a single address for most business entities they worked with. Today, however, one doesn’t seem to be enough. Between dealing with companies that have multiple locations, and even individuals deciding to receive bills at one location, but ship to a different location, many business entities we work with have multiple addresses. The AdventureWorks2008 database represents this by separating addresses out into their own table (Person.Address).We’ve decided that we want to represent this notion of an address in a consistent way across our systems, so we create our custom type:

USE AdventureWorks2008;

GO

CREATE TYPE Person.Address

AS TABLE(

AddressID int NULL,

AddressLine1 nvarchar(60) NOT NULL,

AddressLine2 nvarchar(60) NULL,

City nvarchar(30) NOT NULL,

StateProvinceID int NOT NULL,

PostalCode nvarchar(15) NOT NULL,

SpatialLocation geography NULL

);

There are a host of items to notice about this script:

Ø  I used the exact name of an existing object in the database (there is a table called Person.Address). The type can be considered to be much like the difference between a class and an object— that is, a type is a definition, and a table is an actual instance of something (though, the table is not an instance of the type definition the way an object is an instance of a class).

Ø  The syntax for creating the actual definition is very similar to the CREATE TABLE syntax.

Ø  The layout maps very closely to the Person.Address table in order to support moving data between the two relatively easily.

Note that I created my user-defined type with the same name as a table just to prove the point. I would

not recommend duplicating names in practice, as it is likely to lead to far more confusion than it is worth.

Let’s further our example just a bit by utilizing our new type:

DECLARE @Address Person.Address;

INSERT INTO @Address

(AddressID,AddressLine1,City,StateProvinceID,PostalCode) VALUES

(1,‘My first address’,‘MyTown’,1,‘21212’),

(1,‘My second address’,‘OtherTown’,5,‘43434’),

(1,‘My third address’,‘MyTown’,1,‘21214’);

SELECT * FROM @Address;

Notice that, with a simple declaration of our Person.Address user-defined type, we gained access to all

the columns for that tabular type. We’re able to insert rows, and select them back out:

(3 row(s) affected)

AddressID  AddressLine1

----------- --------------------

1 My first address ...

1 My second address ...

1 My third address ...

(3 row(s) affected)

Again, we’ll take a further look at uses for this in Chapter 10 as part of our table-valued parameter

discussion.

Dropping a User-Defined Type

It’s fairly intuitive, but just to make sure we’ve addressed the point; all varieties of UDTs are dropped using the very common DROP <object type> <object name> syntax:

DROP TYPE [<schema name>.]<type name>[;]

Common Table Expressions (CTEs)

Common Table Expressions (CTE) were first introduced back in SQL Server 2005. They provide a means to refer to a temporary result set by name, and thus utilize it as a table (albeit both temporary and virtual in nature). Perhaps the coolest thing about them is that you define them before actually using them, so you can avoid separate physical steps storing and re-referencing the table (as you would do with a temporary table — or even a table variable). This can have very favorable performance impacts since SQL Server can plan the work between the CTE and the queries that utilize it as part of one logical operation rather than as a series of separate activities. In their simplest form, CTEs are similar to views created on the fly, but a CTE can also enable other things that you can’t really do with a view (for example, see the following section on recursive queries).

The basic syntax for a CTE utilizes the WITH keyword followed by a name and definition:

WITH <CTE name> [( <column name> [,...n])]

AS

( <query returning tabular data> )

<statement that will make use of the CTE>

After the CTE is defined, you can refer to it by name just as if it were a table.

Note that while a CTE can nest, and a CTE can refer to a parent CTE, you cannot have completely inde-

pendent CTEs at the same time, nor can you reference forward in your nested CTEs. Indeed, whatever

statement is going to use the CTE must immediately follow the CTE declaration.

So, as an example of CTE use, we could replace part of our earlier derived table with a CTE reference:

USE AdventureWorks2008;

WITH pumps (BusinessEntityID)

AS

(

SELECT sc.PersonID AS BusinessEntityID

FROM Sales.Customer sc

JOIN Sales.SalesOrderHeader AS soh

ON sc.CustomerID = soh.CustomerID

JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

JOIN Production.Product AS p

ON sod.ProductID = p.ProductID

WHERE p.Name = ‘Minipump’

)

SELECT DISTINCT pp.FirstName, pp.LastName

FROM Person.Person AS pp

JOIN pumps

ON pp.BusinessEntityID = pumps.BusinessEntityID

JOIN ( SELECT sc.PersonID AS BusinessEntityID

FROM Sales.Customer sc

JOIN Sales.SalesOrderHeader AS soh

ON sc.CustomerID = soh.CustomerID

JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

JOIN Production.Product AS p

ON sod.ProductID = p.ProductID

WHERE p.Name = ‘AWC Logo Cap’) caps

ON pp.BusinessEntityID = caps.BusinessEntityID;

So here pumps is a temporary table with column name BusinessEntityID

Notice that I was able to cut the first derived table out entirely and replace it with the CTE reference. I

cannot, however, also replace the caps derived table, as I can only make one CTE reference at a time. I

can replace pumps, or I can replace caps, but not both.

It’s worth noting that certain constructs cannot be used within a CTE. These include:

❑ COMPUTER and COMPUTE BY

❑ ORDER BY

❑ INTO

❑ The FOR XML, FOR BROWSE, and OPTION query clauses