The partitioned view allows you to split the data in a large table into smaller member tables. According to a range of data values, the data to be partitioned between the member tables. The range of data for each member of the table are defined in a CHECK constraint specified for partitioning column. Then define a view that uses UNION ALL the selected member tables into a single result set. The SELECT statement referencing the view for the partitioning column specified search criteria, the query optimizer will use the CHECK constraint definitions to determine which member table contains the corresponding row.
Note: |
---|
The preferred method of the data on the server partition is the partition table. For more information, please refer to . |
For example, the record1998
sales sales table is partitioned into 12 members of the table, a month table. Each member table has constraint defined inOrderMonth
:
CREATE TABLE May1998Sales to (OrderID INT, CustomerID INT NOT NULL, OrderDate DATETIME NULL CHECK (DATEPART (yy, OrderDate) = 1998) OrderMonth INT CHECK (OrderMonth, = 5), DeliveryDate DATETIME NULL CHECK (DATEPART (mm DeliveryDate) in = 5) CONSTRAINT OrderIDMonth PRIMARY KEY (OrderID, OrderMonth) )
FillMay1998sales
application must ensure that the value of all the rows in the theOrderMonth
column , specify a date in mid-May 1998 and the order date.5
This enforce constraints defined by the table.
Then, define a view, the view using theUNION ALL
select data from all 12 member tables as a single result set:
CREATE VIEW Year1998Sales to AS SELECT * FROM Jan1998Sales UNION ALL SELECT * FROM Feb1998Sales UNION ALL SELECT * FROM Mar1998Sales UNION ALL SELECT * FROM Apr1998Sales UNION ALL SELECT * FROM May1998Sales UNION ALL SELECT * FROM Jun1998Sales UNION ALL SELECT * FROM Jul1998Sales UNION ALL SELECT * FROM Aug1998Sales UNION ALL SELECT * FROM Sep1998Sales UNION ALL SELECT * FROM Oct1998Sales UNION ALL SELECT * FROM Nov1998Sales UNION ALL SELECT * FROM Dec1998Sales
For example, the followingSELECT
statement to query information about a specific month.
SELECT * FROM Year1998Sales WHERE OrderMonth IN (5,6) AND CustomerID = 64892
The SQL Server query optimizer can be identified theSELECT
statement search criteria the only referencesMay1998Sales
table andJun1998Sales
table in the line. Therefore, it will limit the search scope in these tables.
Partitioned views updated partitioning column must be part of the primary key of the base table. If the view is not updatable view that allows updates to create an INSTEAD OF trigger. Should trigger design error handling to ensure that no duplicate rows are inserted. For a design example of an INSTEAD OF trigger on a view, see .
The partitioned view returns the correct result does not necessarily require a CHECK constraint. However, If you do not define a CHECK constraint, the query optimizer must search all the tables, rather than search only meet the partition table based on the column on the search criteria. If you do not use the CHECK constraint, the view operates in the same way as any other use UNION ALL view. The query optimizer can not make any assumption as to the value stored in different tables can not skip the table involved in the view definition search.
If the partition all the member tables referenced by the view on the same server, the view is a local partitioned view. If the member tables on multiple servers, the view is distributed partitioned view. Distributed partitioned view can be used for distributed database processing load among a group of servers. For more information, please refer to the .
Partitioned view can be more easily maintained independently of the members of the table. For example, at the end of a period can do the following:
- Can change the results of the partitioned view definition to add the latest stage and delete the earliest possible stage.
- Partitioned view definitions can change the previous results to add just the current results view deleted stage. You can also update the previous results view to delete or archive the views contained in the earliest stages.
Insert the data into a partitioned view, you can use the sp_executesql system stored procedure to create the INSERT statement, the statement contains a higher risk of execution plan re-use in systems with many concurrent users.