SQL Server分区视图

SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily. A common example for tables partitioning is archiving old data to slow disk drives and use the fast ones to store the frequently accessed data. Table partitioning improves query performance by excluding the partitions that are not needed in the result set. But table partitioning is available only in the Enterprise SQL Server Edition, which is not easy to upgrade to for most of small and medium companies due to its expensive license cost.

SQL Server表分区是一项很棒的功能，可用于透明地将大表拆分为多个小表。 它使您可以将数据存储在多个文件组中，并将数据库文件保留在不同的磁盘驱动器中，并能够轻松地将数据移入和移出分区表。 表分区的一个常见示例是将旧数据存档到慢速磁盘驱动器，并使用快速磁盘驱动器存储经常访问的数据。 表分区通过排除结果集中不需要的分区来提高查询性能。 但是表分区仅在Enterprise SQL Server Edition中可用，由于许可证成本昂贵，因此对于大多数中小型公司而言，将其升级并不容易。

Fortunately, SQL Server allows you to design your own partitioning solution without the need to upgrade your current SQL Server instance to Enterprise Edition. This new option is called Partitioned Views. Although this new solution is not as flexible as table partitioning, Partitioned Views will give you a good result if you design it properly. It is up to you to manually design the tables that will work as partitions and combine it together using the UNION ALL operator in the Partitioned View that will work like table partitioning.

幸运的是，SQL Server允许您设计自己的分区解决方案，而无需将当前SQL Server实例升级到企业版。 此新选项称为分区视图 。 尽管此新解决方案不如表分区灵活，但如果设计正确，分区视图将为您带来良好的效果。 您可以手动设计将用作分区的表，并在分区视图中使用UNION ALL运算符将它们组合在一起，从而像表分区一样工作。

SQL Server Partitioned Views enable you to logically split a huge amount of data that exist in large tables into smaller pieces of data ranges, based on specific column values, and store this data ranges in the participating tables. To achieve this, a CHECK constraint should be defined on the partitioning column to divide the data into data ranges. Then, a partitioned view that combines SELECTs from all participating tables as one result set using UNION ALL operator, will be created. The CHECK constraint is used to specify which table contains the requested data when selecting data from the view, which is similar to defining the portioning function in the table partitioning feature. The check constraint is used also to improve query performance, If the CHECK constraint is not defined in the participating tables, the SQL Server Query Optimizer will search in all participating tables within the view to return the result.

SQL Server分区视图使您可以根据特定的列值在逻辑上将大型表中存在的大量数据拆分为较小的数据范围，并将此数据范围存储在参与的表中。 为此，应在分区列上定义CHECK约束，以将数据划分为数据范围。 然后，将创建一个分区视图，该分区视图使用UNION ALL运算符将所有参与表的SELECT合并为一个结果集。 当从视图中选择数据时，CHECK约束用于指定哪个表包含请求的数据，这类似于在表分区功能中定义分区功能。 检查约束还用于提高查询性能。如果未在参与表中定义CHECK约束，则SQL Server查询优化器将在视图内的所有参与表中搜索以返回结果。

Partitioned Views don’t require defining a partitioning schema or function, have no specific syntax to swap the data in and out the tables and no need to figure out the RIGHT and LEFT data boundaries. In the same way as table partitioning, you can create each table in the Partitioned View in a separate filegroup on a different disk drive, where you can locate the old archived data in a slow drive and locate the most active data in a fast drive such as the solid state disks. But in an opposite approach to table partitioning, each table that participates in the Partitioned View is an individual story that you can ALTER or CREATE INDEX on individually and the tables can have different columns. Adding a new table to the Partitioned view can be done easily by modifying the view definition to include that new table.

分区视图不需要定义分区模式或函数，不需要特定的语法即可将数据交换到表中和从表中交换出来，也不需要弄清楚RIGHT和LEFT数据边界。 以与表分区相同的方式，可以在分区视图中的每个表中的每个文件组中的每个表中创建每个表，在该文件组中，您可以在慢速驱动器中找到旧的存档数据，并在快速驱动器中找到最活跃的数据，例如作为固态磁盘。 但是以相反的方式进行表分区，每个参与分区视图的表都是一个单独的故事，您可以单独对ALTER或CREATE INDEX进行更改，并且这些表可以具有不同的列。 通过修改视图定义以包括该新表，可以轻松地向分区视图中添加新表。

Partitioned Views allow you to perform data changes on the partitioned view itself which will handle any changes in participating tables. To be able to update from the view, the view should combine the SELECTs from the participating tables using the UNION ALL operator, where each SELECT references to one of these local or linked tables and the partitioning column should be a part of the primary key on each participating table. If all the Partitioned View participating tables are located on the same SQL Server, the view is referred to as a Local Partitioned View. A Distributed Partitioned View contains participating tables from multiple SQL Server instances, which can be used to distribute the data processing load across multiple servers. Another advantage for the SQL Server Partitioned Views is that the underlying tables can participate in more than one Partitioned View, which could be helpful in some implementations.

分区视图允许您对分区视图本身执行数据更改，该更改将处理参与表中的所有更改。 为了能够从视图中进行更新，该视图应使用UNION ALL运算符组合参与表中的SELECT，其中每个对这些本地或链接表之一的SELECT引用以及分区列都应是主键的一部分。每个参与表。 如果所有“分区视图”参与表都位于同一SQL Server上，则该视图称为“ 本地分区视图” 。 分布式分区视图包含来自多个SQL Server实例的参与表，可用于在多个服务器之间分配数据处理负载。 SQL Server分区视图的另一个优点是，基础表可以参与多个分区视图，这在某些实现中可能会有所帮助。

Let’s go through our demo to understand practically how to implement the SQL Server Partitioned View and its benefits. We will start with creating four new tables under SQLShackDemo database with identical schema and each table keeps the shipments information for a specific quarter of the year. The partitioning column is the Ship_Quarter column in which the constraint that specifies the four quarters is defined. The Ship_Quarter column is also included in the Primary Key constraint. The T-SQL script to create the four quarters tables will be like:

让我们通过演示来实际了解如何实现SQL Server分区视图及其好处。 我们将从在SQLShackDemo数据库下创建具有相同架构的四个新表开始，每个表保留一年中特定季度的发货信息。 分区列是Ship_Quarter列，其中定义了指定四个季度的约束。 Ship_Quarter列也包含在主键约束中。 用于创建四个四分之一表的T-SQL脚本如下所示：

 
USE SQLShackDemo 
GO
CREATE TABLE Shipments_Q1 (
Ship_Num INT NOT NULL,
Ship_CountryCode CHAR(3) NOT NULL,
Ship_Date DATETIME NULL,
Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q1 CHECK (Ship_Quarter = 1),
CONSTRAINT PK_Shipments_Q1 PRIMARY KEY (Ship_Num, Ship_Quarter)
);
 
GO
CREATE TABLE Shipments_Q2 (
Ship_Num INT NOT NULL,
Ship_CountryCode CHAR(3) NOT NULL,
Ship_Date DATETIME NULL,
Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q2 CHECK (Ship_Quarter = 2),
CONSTRAINT PK_Shipments_Q2 PRIMARY KEY (Ship_Num, Ship_Quarter)
);
 
GO
CREATE TABLE Shipments_Q3 (
Ship_Num INT NOT NULL,
Ship_CountryCode CHAR(3) NOT NULL,
Ship_Date DATETIME NULL,
Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q3 CHECK (Ship_Quarter = 3),
CONSTRAINT PK_Shipments_Q3 PRIMARY KEY (Ship_Num, Ship_Quarter)
);
 
GO
CREATE TABLE Shipments_Q4 (
Ship_Num INT NOT NULL,
Ship_CountryCode CHAR(3) NOT NULL,
Ship_Date DATETIME NULL,
Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q4 CHECK (Ship_Quarter = 4),
CONSTRAINT PK_Shipments_Q4 PRIMARY KEY (Ship_Num, Ship_Quarter)
);
 

Once the tables created successfully, we will create the Partitioned View that combines four the SELECT statement, one per each participating table, using the UNION ALL T-SQL statement as in the below CREATE VIEW statement:

一旦成功创建了表，我们将使用UNION ALL T-SQL语句创建分区视图，该视图结合了四个SELECT语句，每个参与表一个，如下所示，该CREATE VIEW语句：

 
USE SQLShackDemo 
GO
CREATE VIEW DBO.Shipments_Info
WITH SCHEMABINDING
AS
SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q1
UNION ALL
SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q2
UNION ALL
SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q3
UNION ALL
SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q4
 

That’s it! Now the Partitioned View solution is ready to use. If we try to insert four values to the view directly, which is updatable in our case here as the view contains SELECT statement per each participant table, the tables combined by UNION ALL operator and the partitioning column is involved in the Primary Key constraint as described previously. The INSERT INTO T-SQL statement to the view will handle spreading the data through the participating tables depending on the CHECK constraint. If we run the below INSERT INTO statements:

而已！ 现在，可以使用分区视图解决方案了。 如果我们尝试直接向视图中插入四个值，在此例中这是可更新的，因为该视图在每个参与者表中都包含SELECT语句，则由UNION ALL运算符和分区列组合的表将包含在主键约束中，如所述先前。 对视图的INSERT INTO T-SQL语句将根据CHECK约束处理通过参与表传播数据。 如果我们运行下面的INSERT INTO语句：

 
INSERT INTO DBO.Shipments_Info VALUES(1117,'JOR',GETDATE(),1)
INSERT INTO DBO.Shipments_Info VALUES(1118,'JFK',GETDATE(),2)
INSERT INTO DBO.Shipments_Info VALUES(1119,'CAS',GETDATE(),3)
INSERT INTO DBO.Shipments_Info VALUES(1120,'BEY',GETDATE(),4)
 

And try to retrieve the data from the four participating tables:

并尝试从四个参与表中检索数据：

 
SELECT * FROM DBO.Shipments_Q1
GO
SELECT * FROM DBO.Shipments_Q2
GO
SELECT * FROM DBO.Shipments_Q3
GO
SELECT * FROM DBO.Shipments_Q4
GO
 

The result will show us that one row will be inserted on each participating table depending on the Ship_Quarter column value as follows:

结果将向我们显示，将根据Ship_Quarter列值在每个参与表上插入一行，如下所示：

Also, running a direct SELECT statement from the view itself:

另外，从视图本身运行直接SELECT语句：

 
  SELECT * FROM DBO.Shipments_Info
 

Will combine each table’s result into one result set, showing the four inserted columns as below:

将每个表的结果合并为一个结果集，显示插入的四列，如下所示：

It is clear from the execution plan generated using the APEXSQL PLAN application that the SQL Server Query Optimizer will scan each participating table and finally concatenate all results into one result set using the UNION ALL operator as in the below execution plan:

从使用APEXSQL PLAN应用程序生成的执行计划中可以明显看出，SQL Server查询优化器将扫描每个参与的表，最后使用UNION ALL运算符将所有结果连接到一个结果集中，如以下执行计划所示：

If we plan to retrieve the third quarter data using a filter on the Ship_Num column:

如果我们计划使用Ship_Num列上的过滤器检索第三季度数据：

 
  SELECT * FROM DBO.Shipments_Info WHERE [Ship_Num] = 1119
 

The SQL Server Query Optimizer will seek all participating tables for this value, which is part of the Primary Key on all tables, in order to retrieve the requested data as in the execution plan below generated using the APEXSQL PLAN application:

SQL Server查询优化器将在所有参与的表中寻找该值，该值是所有表上主键的一部分，以便如下面使用APEXSQL PLAN应用程序生成的执行计划中那样检索请求的数据：

But if we search for the same data by filtering the Ship_Quarter partitioning column:

但是，如果我们通过过滤Ship_Quarter分区列来搜索相同的数据：

 
  SELECT * FROM DBO.Shipments_Info WHERE [Ship_Quarter] = 3
 

The SQL Server Query Optimizer will identify directly in which table it can find that data without the need to touch all participating tables as shown in the below execution plan generated using the APEXSQL PLAN application:

SQL Server查询优化器将直接识别它可以在哪个表中找到数据，而无需触摸所有参与的表，如使用APEXSQL PLAN应用程序生成的以下执行计划所示：

Comparing the two queries performance by running it at the same time and enabling the STATISTICS TIME counter:

通过同时运行两个查询性能并启用STATISTICS TIME计数器来比较两个查询的性能：

 
SET STATISTICS TIME ON
SELECT * FROM DBO.Shipments_Info WHERE [Ship_Num] = 1119
GO
SELECT * FROM DBO.Shipments_Info WHERE [Ship_Quarter] = 3
SET STATISTICS TIME OFF
 

It is clear from the result that using the partitioning column included in the CHECK constraint to filter the retrieved data will enhance the query performance four times in our situation here, as it will visit only the table that contains the requested data, which is become easier by defining the CHECK constraint in the participating tables. The below figure compares the performance of both queries execution plans:

从结果很明显，在我们这里的情况下，使用CHECK约束中包括的分区列来过滤检索到的数据将提高查询性能四倍，因为它将仅访问包含所请求数据的表，这变得更加容易通过在参与表中定义CHECK约束。 下图比较了两个查询执行计划的性能：

Also the time required to execute the first query is four to five times the time required to execute the second query searching on the partitioning column:

同样，执行第一个查询所需的时间是在分区列上执行第二个查询所需的时间的四到五倍：

And the real difference can be touched when dealing with tables that contain millions of rows.

处理包含数百万行的表时，可以感受到真正的区别。

翻译自: https://www.sqlshack.com/sql-server-partitioned-views/