USE Northwind
GO

CREATE TABLE myTable99_1 (
	  Account	char(3)
	, Ledger	char(4)
	, PostDate	datetime
	, PRIMARY KEY (Account, Ledger)
	, CHECK(PostDate> '1/1/1999' and PostDate < '12/31/1999 23:59:59'))
CREATE TABLE myTable99_2 (
	  Account	char(3)
	, Ledger	char(4)
	, PostDate	datetime
	, PRIMARY KEY (Account, Ledger)
	, CHECK(PostDate> '1/1/2000' and PostDate < '12/31/2000 23:59:59'))
CREATE TABLE myTable99_3 (
	  Account	char(3)
	, Ledger	char(4)
	, PostDate	datetime
	, PRIMARY KEY (Account, Ledger)
	, CHECK(PostDate> '1/1/2001' and PostDate < '12/31/2001 23:59:59'))

CREATE INDEX myTable99_1_IX ON MyTable99_1
	(Account, Ledger)
CREATE INDEX myTable99_2_IX ON MyTable99_2
	(Account, Ledger)
CREATE INDEX myTable99_3_IX ON MyTable99_3
	(Account, Ledger)
GO

CREATE VIEW myView99
AS
SELECT 	  Account
	, Ledger
	, PostDate
  FROM myTable99_1
UNION ALL
SELECT 	  Account
	, Ledger
	, PostDate
  FROM myTable99_2
UNION ALL
SELECT 	  Account
	, Ledger
	, PostDate
  FROM myTable99_3
GO

SELECT * FROM myView99 WHERE Account = 1 AND Ledger = 1
GO

DROP VIEW myView99
DROP TABLE myTable99_1,  myTable99_2,  myTable99_3
GO

quote:

---

To perform updates on a partitioned view, the partitioning column must be a part of the primary key of the base table.

---

CHECK(PostDate > '1/1/1999' and ...

quote:

---

Table Rules
Member tables are defined in the FROM clause in each SELECT statement in the view definition. Each member table must adhere to these rules:

- Member tables cannot be referenced more than once in the view.

- Member tables cannot have indexes created on any computed columns.

- Member tables must have all PRIMARY KEY constraints on an identical number of columns.

- Member tables must have the same ANSI padding setting. For more information about the ANSI padding setting, see SET ANSI_PADDING.

Column Rules
Columns are defined in the select list of each SELECT statement in the view definition. The columns must follow these rules.

- All columns in each member table must be included in the select list. SELECT * FROM <member table> is acceptable syntax.

- Columns cannot be referenced more than once in the select list.

- The columns must be in the same ordinal position in the select list

- The columns in the select list of each SELECT statement must be of the same type (including data type, precision, scale, and collation). For example, this view definition fails because the first column in both SELECT statements does not have the same data type:

CREATE VIEW NonUpdatable
AS
SELECT IntPrimaryKey, IntPartNmbr
FROM FirstTable
  UNION ALL
SELECT NumericPrimaryKey, IntPartNmbr
FROM SecondTable

Partitioning Column Rules
A partitioning column exists on each member table and, through CHECK constraints, identifies the data available in that specific table. Partitioning columns must adhere to these rules:

- Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

- The partitioning column cannot be an identity, default or timestamp column.

- The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view. For example, the partitioning column is always the first column in each select list, or the second column in each select list, and so on.

- Partitioning columns cannot allow nulls.

- Partitioning columns must be a part of the primary key of the table.

- Partitioning columns cannot be computed columns.

- There must be only one constraint on the partitioning column. If there is more than one constraint, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view.

- There are no restrictions on the updatability of the partitioning columns.

---