Modifying Data in Partitioned Views

  • Article

If a partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.

A view is considered an updatable partitioned view when the view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced by using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

Additionally, data modification statements referencing the view must follow the rules defined for INSERT, UPDATE and DELETE statements.

If the partitioned view is distributed across servers, avoid using triggers or cascading actions on the base tables. A trigger or cascading action could make changes to the underlying data that may affect the view definition.

Distributed partitioned views can only be updated if the user has CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION permission on each table underlying the view. For more information, see Troubleshooting Metadata Visibility of Distributed Partitioned Views.

Note

You can modify data through a distributed partitioned view only if you install Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition. However, you can modify data through a local partitioned view on any edition of SQL Server 2005.

INSERT Statements

INSERT statements add data to the member tables through the partitioned view. The INSERT statements must follow to these rules:

  • All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.
  • The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.
  • INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.
  • INSERT statements are not allowed if a member table contains a column with an identity property.
  • INSERT statements are not allowed if a member table contains a timestamp column.
  • INSERT statements are not allowed if there is a self-join with the same view or any one of the member tables.

UPDATE Statements

UPDATE statements modify data in one or more of the member tables through the partitioned view. The UPDATE statements must follow to these rules:

  • UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table
  • The value of a column with an identity property cannot be changed: however, the other columns can be updated.
  • The value of a PRIMARY KEY cannot be changed if the column contains text, image, or ntext data.
  • Updates are not allowed if a base table contains a timestamp column.
  • Updates are not allowed if there is a self-join with the same view or any one of the member tables.

DELETE Statements

DELETE statements remove data in one or more of the member tables through the partitioned view. DELETE statements are not allowed when there is a self-join with the same view or any one of the member tables.

See Also

Concepts

Implementing Federated Database Servers

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005
New content:
  • Added information about the necessary permissions to update a distributed partitioned view.
Changed content:
  • Removed the following text: "For a partitioned view that is local to one server, the view is not updatable when a trigger or cascading update or delete is defined on one or more member tables."