Partitioned View - Rules

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, 

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

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.