SQLServer之创建分区视图

时间:2024-04-10 23:07:28

分区视图定义

分区视图是通过对成员表使用 UNION ALL 所定义的视图,这些成员表的结构相同,但作为多个表分别存储在同一个 SQL Server实例中,或存储在称为联合数据库服务器的自主 SQL Server 服务器实例组中。

对一个服务器的本地数据进行分区的首选方法是通过分区表。

不能使用SSMS数据库管理工具创建分区视图,可以使用Transact-SQL脚本创建分区视图。

创建分区视图的条件

1、选择 list

  • 应在视图定义的列列表中选择成员表中的所有列。
  • 每个 select list 中的同一序号位置上的列应属于同一类型,包括排序规则。 列仅仅属于可隐式转换的类型(如通常情况下的 UNION)是不够的。
  • 约束应按照以下方式定义:<col> 的任何指定值最多只能满足一个 C1, ..., Cn 约束,从而使约束形成一组不联接或不重叠的间隔。 定义不联接的约束的列 <col> 称为分区列。 请注意,分区列在基础表中可能有不同的名称。 约束应处于启用和信任状态,以使它们满足分区列的上述条件。 如果约束被禁用,则使用 ALTER TABLE 的 CHECK CONSTRAINT constraint_name 选项重新启用约束检查,并使用 WITH CHECK 选项对其进行验证。
  • 在选择列表中不能多次使用同一列。

2、分区列

  • 分区列是表的 PRIMARY KEY 的一部分。

  • 分区列不能是计算列、标识列、默认列或 timestamp 列。

  • 如果成员表中的同一列上存在多个约束,则数据库引擎将忽略所有约束,且在确定视图是否为分区视图时不考虑这些约束。 若要满足分区视图的条件,在分区列上应只有一个分区约束。

  • 分区列的可更新性没有限制。

3、成员表或基础表 T1, ..., Tn

  • 表可以是本地表,也可以是通过由四部分组成的名称或基于 OPENDATASOURCE 或 OPENROWSET 的名称引用的运行SQL Server 的其他计算机中的表。 OPENDATASOURCE 和 OPENROWSET 语法可以指定表名,但不能指定直接传递查询。 有关详细信息,请参阅 OPENDATASOURCE (Transact-SQL) 和 OPENROWSET (Transact-SQL)

    如果一个或多个成员表是远程表,则视图将被称为“分布式分区视图”,并且将应用附加条件。 本部分后面将对其进行说明。

  • 在用 UNION ALL 语句组合的表集合中,同一个表不能出现两次。

  • 成员表不能对表中的计算列创建索引。

  • 成员表在编号相同的列上应具有所有 PRIMARY KEY 约束。

  • 视图中的所有成员表都应具有相同的 ANSI 填充设置。 这可以使用 sp_configure 中的 user options 选项或 SET 语句进行设置。

使用T-SQL脚本创建分区视图

语法:

--声明数据库引用
use 数据库名;
go

--判断是否存在分区视图,如果存在则删除
if exists(select * from sys.views where name=视图名称)
drop view 视图名称;
go

--创建分区视图
create
view

--视图所属架构的名称。
--[schema_name][.]
[dbo].视图名称

--视图中的列使用的名称。 仅在下列情况下需要列名:列是从算术表达式、函数或常量派生的;两个或更多的列可能会具有相同的名称(通常是由于联接的原因);视图中的某个列的指定名称不同于其派生来源列的名称。 还可以在 SELECT 语句中分配列名。
--如果未指定 column,则视图列将获得与 SELECT 语句中的列相同的名称。
(列名,列名,.......)

[with]

--适用范围: SQL Server 2008 到 SQL Server 2017 和 Azure SQL Database。
--对 sys.syscomments 表中包含 CREATE VIEW 语句文本的项进行加密。 使用 WITH ENCRYPTION 可防止在 SQL Server 复制过程中发布视图。
[encryption][,]

--指定为引用视图的查询请求浏览模式的元数据时, SQL Server 实例将向 DB-Library、ODBC 和 OLE DB API 返回有关视图的元数据信息,而不返回基表的元数据信息。
--浏览模式元数据是 SQL Server 实例向这些客户端 API 返回的附加元数据。 如果使用此元数据,客户端 API 将可以实现可更新客户端游标。 浏览模式的元数据包含结果集中的列所属的基表的相关信息。
--对于使用 VIEW_METADATA 创建的视图,浏览模式的元数据在描述结果集内视图中的列时,将返回视图名,而不返回基表名。
--当使用 WITH VIEW_METADATA 创建视图时,如果该视图具有 INSTEAD OF INSERT 或 INSTEAD OF UPDATE 触发器,则视图的所有列(timestamp 列除外)都可更新。 有关可更新视图的详细信息,请参阅“备注”。
[view_metadata]

as
select_statement
go

示例:

--声明数据库引用
use testss;
go

--判断是否存在分区视图,如果存在则删除
if exists(select * from sys.views where name='partitionview')
drop view partitionview;
go

--创建分区视图
create
view

--视图所属架构的名称。()
--[schema_name][.]
dbo.partitionview

--视图中的列使用的名称。 仅在下列情况下需要列名:列是从算术表达式、函数或常量派生的;两个或更多的列可能会具有相同的名称(通常是由于联接的原因);视图中的某个列的指定名称不同于其派生来源列的名称。 还可以在 SELECT 语句中分配列名。
--如果未指定 column,则视图列将获得与 SELECT 语句中的列相同的名称。
(name,sex)

with

--适用范围: SQL Server 2008 到 SQL Server 2017 和 Azure SQL Database。
--对 sys.syscomments 表中包含 CREATE VIEW 语句文本的项进行加密。 使用 WITH ENCRYPTION 可防止在 SQL Server 复制过程中发布视图。
encryption,

--指定为引用视图的查询请求浏览模式的元数据时, SQL Server 实例将向 DB-Library、ODBC 和 OLE DB API 返回有关视图的元数据信息,而不返回基表的元数据信息。
--浏览模式元数据是 SQL Server 实例向这些客户端 API 返回的附加元数据。 如果使用此元数据,客户端 API 将可以实现可更新客户端游标。 浏览模式的元数据包含结果集中的列所属的基表的相关信息。
--对于使用 VIEW_METADATA 创建的视图,浏览模式的元数据在描述结果集内视图中的列时,将返回视图名,而不返回基表名。
--当使用 WITH VIEW_METADATA 创建视图时,如果该视图具有 INSTEAD OF INSERT 或 INSTEAD OF UPDATE 触发器,则视图的所有列(timestamp 列除外)都可更新。 有关可更新视图的详细信息,请参阅“备注”。
view_metadata

as
--查询时避免使用*号,升级架构或者增删字段时会出错
select a.name,a.sex from [testss].[dbo].test1 as a
union all
select b.name,b.sex from [Tests].[dbo].test1 as b
go

示例结果:

分区视图创建结果。

SQLServer之创建分区视图

分区视图使用结果。

SQLServer之创建分区视图