Example of Partitioned View by Date

Let’s begin with the creation of the partitioned tables:


IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE NAME = 'PartitionedTable1' )
            DROP TABLE [dbo].[PartitionedTable1]
GO
IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE NAME = 'PartitionedTable2' )
            DROP TABLE [dbo].[PartitionedTable2]
GO

-- TABLE CREATION
CREATE TABLE [dbo].[PartitionedTable1](
    ID INT NOT NULL,
    Date DATETIME NOT NULL CHECK (Date BETWEEN '20090101' AND '20090131'), -- Partitioned Column Check !
    Field1 VARCHAR(25) NULL,
    Field2 VARCHAR(25) NULL,
    CONSTRAINT [PT1_PK] PRIMARY KEY CLUSTERED (ID,Date)
)
CREATE TABLE [dbo].[PartitionedTable2](
    ID INT NOT NULL,
    Date DATETIME NOT NULL CHECK (Date BETWEEN '20090201' AND '20090228'), -- Partitioned Column Check !
    Field1 VARCHAR(25) NULL,
    Field2 VARCHAR(25) NULL,
    CONSTRAINT [PT2_PK] PRIMARY KEY CLUSTERED (ID,Date)
)


Some Notes:
* Partitioned field (Date) should be part of the PK of the table.
* The CHECK constraint can only use these operators:BETWEEN, AND, OR, <, <=, >, >=, =.

Now, let’s create the view for our recently created partitioned tables:

-- VIEW
IF EXISTS (
    SELECT *
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[PartitionedView]') AND type in (N'V', N'PC'))
        DROP VIEW [dbo].[PartitionedView]
GO

CREATE VIEW [dbo].[PartitionedView]
AS
    SELECT *
    FROM [dbo].[PartitionedTable1]
    UNION ALL
    SELECT *
    FROM [dbo].[PartitionedTable2]


voilà! Let’s run a simple test:

-- TEST
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (1,'01/05/2009','Value1','Value2')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (2,'02/10/2009','Value3','Value4')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (3,'01/12/2009','Value5','Value6')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (4,'03/12/2009','Value7','Value8') -- This one FAILS because the date in the new row is not considered by any of the conditions


and check our partitined tables.

SELECT * FROM [dbo].[PartitionedTable1]
SELECT * FROM [dbo].[PartitionedTable2]

You should get something like this:

Enjoy.