Implementing partitioned tables in SQL Server 2008 (or superior) Standard Edition

In this examples it's shown how to partition data between tables by Months of Year and at the same time having just one view to select from and to insert into.

First of all, three tables are created with the same structure for the months: Jan/2012, Feb/2012 and Mar/2012.
Constraint check will keep each table with its respectively data on insert. It seems to work on update as well, but further tests are needed to ensure.

CREATE TABLE [dbo].[Data_2012_01]
(
 [DateTime] DATETIME2(3) NOT NULL,
 [TagId] INT NOT NULL,
 CONSTRAINT CK_Data_2012_01 CHECK ([DateTime] >= CAST('2012-01-01' AS DATETIME2) AND [DateTime] < CAST('2012-02-01' AS DATETIME2)),
 [Value] FLOAT NOT NULL
 CONSTRAINT PK_Data_2012_01 PRIMARY KEY ([DateTime], [TagId])
)
CREATE TABLE [dbo].[Data_2012_02]
(
 [DateTime] DATETIME2(3) NOT NULL,
 [TagId] INT NOT NULL,
 CONSTRAINT CK_Data_2012_02 CHECK ([DateTime] >= CAST('2012-02-01' AS DATETIME2) AND [DateTime] < CAST('2012-03-01' AS DATETIME2)),
 [Value] FLOAT NOT NULL
 CONSTRAINT PK_Data_2012_02 PRIMARY KEY ([DateTime], [TagId])
)
CREATE TABLE [dbo].[Data_2012_03]
(
 [DateTime] DATETIME2(3) NOT NULL,
 [TagId] INT NOT NULL,
 CONSTRAINT CK_Data_2012_03 CHECK ([DateTime] >= CAST('2012-03-01' AS DATETIME2) AND [DateTime] < CAST('2012-04-01' AS DATETIME2)),
 [Value] FLOAT NOT NULL
 CONSTRAINT PK_Data_2012_03 PRIMARY KEY ([DateTime], [TagId])
)

Second, a view is created:

CREATE VIEW [dbo].[Data] WITH SCHEMABINDING AS
SELECT [DateTime], [TagId], [Value] FROM [dbo].[Data_2012_01]
UNION ALL
SELECT [DateTime], [TagId], [Value] FROM [dbo].[Data_2012_02]
UNION ALL
SELECT [DateTime], [TagId], [Value] FROM [dbo].[Data_2012_03]

Third (optional), some data are inserted into that view:

INSERT INTO [dbo].[Data] ([DateTime], [TagId], [Value])
VALUES ('2012-01-01', 1, 1)
 
INSERT INTO [dbo].[Data] ([DateTime], [TagId], [Value])
VALUES ('2012-02-01', 1, 1)

INSERT INTO [dbo].[Data] ([DateTime], [TagId], [Value])
VALUES ('2012-03-01', 1, 1)

At last, execution plan is checked to ensure performance gain.

SELECT [DateTime], [TagId], [Value] FROM [dbo].[Data_2012_02] WHERE [DateTime] BETWEEN '2012-02-03' AND '2012-02-04'
SELECT [DateTime], [TagId], [Value] FROM [dbo].[Data] WHERE [DateTime] BETWEEN '2012-02-03' AND '2012-02-04'

Regardless all unions, the view searched just in the right tables.