sql server分表 sql server分区视图

目录

• 【1】sql server表分区与分表的关系
  • （1.1）sql server表分区
• 【2】分表与分区视图
  • （2.1）构建分表与分区视图
  • （2.2）对分区视图操作：根据主键与分区列自动插入到对应表
  • （2.3）查询这个视图
  • （2.4）正确的查询视图方式（加上分区列）
• 【参考文档】

 

---

回到顶部

【1】sql server表分区与分表的关系

（1.1）sql server表分区

SQL Server 表分区是一项很棒的功能，可用于透明地将大表拆分为多个较小的表。

　　它允许您将数据存储在多个文件组中，并将数据库文件保存在不同的磁盘驱动器中，并且能够轻松地将数据移入和移出分区表。

　　表分区的一个常见示例是将旧数据归档到慢速磁盘驱动器并使用快速磁盘驱动器存储经常访问的数据。表分区通过排除结果集中不需要的分区来提高查询性能。

　　但是表分区仅在企业 SQL Server 版中可用，由于其昂贵的许可证成本，对于大多数中小型公司来说，升级到它并不容易。

（1.2）sql server分区视图-- Partitioned Views（基于分表）

幸运的是，SQL Server 允许您设计自己的分区解决方案，而无需将当前的 SQL Server 实例升级到企业版。

　　这个新选项称为Partitioned Views。虽然这个新的解决方案不像表分区那样灵活，但如果设计得当，分区视图会给你一个很好的结果。

　　您可以手动设计将用作分区的表，并使用分区视图中的 UNION ALL 运算符将它们组合在一起，这将像表分区一样工作。

　　SQL Server 分区视图使您能够根据特定列值在逻辑上将存在于大型表中的大量数据拆分为较小的数据范围，并将这些数据范围存储在参与的表中。

　　为此，应在分区列上定义 CHECK 约束以将数据划分为数据范围。

然后，将创建一个分区视图，该视图使用 UNION ALL 运算符将来自所有参与表的 SELECT 组合为一个结果集。

　　CHECK 约束用于在从视图中选择数据时指定哪个表包含请求的数据，类似于在表分区特性中定义分块函数。检查约束还用于提高查询性能，如果参与表中未定义 CHECK 约束，

回到顶部

【2】分表与分区视图

（2.1）构建分表与分区视图

-- 构建分表
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)
);
go

-- 构建分区视图
CREATE VIEW DBO.Shipments_Info
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
GO

那么如上面代码，我们这里的 主键是 ship_num,ship_quarter 的组合主键，与表分区对比的话，ship_quarter 就是分区列；

（2.2）对分区视图操作：根据主键与分区列自动插入到对应表

-- 插入视图，自动对应插入到表中去了 
insert into Shipments_Info values(1,1,getdate(),1)
select * from Shipments_Q1

　　

 

（2.3）查询这个视图

如下图，2个图，无论是 左边还是右边，但我们查询的时候是在视图中的四个表都会去查询的；

　　  

 

（2.4）正确的查询视图方式（加上分区列）

如下图，用分区列查询，我们就可以精准命中

 　　

 

就此达到了 找到对应实际表操作，精准命中了操作访问范围 从4个表 变成了1个表；

回到顶部

【参考文档】

转自：https://www.sqlshack.com/sql-server-partitioned-views/