经验:
当组成分区表的小表结构发生变化,必须要删除分区表,然后每个小表的约束也要删除然后重建约束,即使约束没有改变,也必须这样操作,全部小表约束重建完成,再重新建立分区表.建立分区表时,可能直接写入分区表的组成语句会失败,提示"视图定义中没有输出列或 FROM 子句中没有项目",只有先建立一个单表的分区表,然后双击分区视图,修改分区表的语句,才能成功,采用"设计视图"功能修改语句总是会失败,原因是"CREATE VIEW 视图名"这行语句没有.
另外分区视图的更新,必须要每个字段都显示给出值,不能采用默认值.
有时候发现无法在分区表中删除行,也是因为小表的约束问题造成,必须要删除约束重建约束,重建分区视图.
------------------------------------
主要功能是为了提高大数据量性能,通过把数据放到不同表,或不能同服务器的不同数据库实例上。来提高数据性能。
下面看一个实际,转自WEB CAST,
在服务器A创建表CUSTOMERS,
// ServerA
Use pubs
go
Create table customers (
Customerid varchar ( 5 ) not null ,
CompanyName varchar ( 50 ) not null ,
ContactName varchar ( 30 ) null ,
CONSTRAINT PK_customers PRIMARY KEY CLUSTERED (Customerid),
CONSTRAINT CK_customerid CHECK (Customerid between ' AAAAA ' and ' LZZZZ ' )
)
在服务器B也创建另一张表,表的结果,类型完全一样,注意表的CHECK不同。(表名可以不同,但最好一样)
//
Server B
use pubs
go
Create table customers (
Customerid varchar ( 5 ) not null ,
CompanyName varchar ( 50 ) not null ,
ContactName varchar ( 30 ) null ,
CONSTRAINT PK_customers PRIMARY KEY CLUSTERED (Customerid),
CONSTRAINT CK_customerid CHECK (Customerid between ' M ' and ' ZZZZZ ' )
)
go
use pubs
go
Create table customers (
Customerid varchar ( 5 ) not null ,
CompanyName varchar ( 50 ) not null ,
ContactName varchar ( 30 ) null ,
CONSTRAINT PK_customers PRIMARY KEY CLUSTERED (Customerid),
CONSTRAINT CK_customerid CHECK (Customerid between ' M ' and ' ZZZZZ ' )
)
go
在A服务器上创建B服务器的链接服务器,已使A服务器能访问B服务器,
//
ServerA
exec sp_addlinkedserver
@server = ' DPVSERVER1 ' , @srvproduct = '' ,
@provider = ' SQLOLEDB ' , @datasrc = ' ServerB '
go
exec sp_addlinkedsrvlogin
@rmtsrvname = ' DPVSERVER1 '
, @useself = ' false '
, @rmtuser = ' sa '
, @rmtpassword = ' password '
go
exec sp_addlinkedserver
@server = ' DPVSERVER1 ' , @srvproduct = '' ,
@provider = ' SQLOLEDB ' , @datasrc = ' ServerB '
go
exec sp_addlinkedsrvlogin
@rmtsrvname = ' DPVSERVER1 '
, @useself = ' false '
, @rmtuser = ' sa '
, @rmtpassword = ' password '
go
同样,B服务器,创建A服务器的链接服务器,
//
ServerB
exec sp_addlinkedserver
@server = ' DPVSERVER2 ' , @srvproduct = '' ,
@provider = ' SQLOLEDB ' , @datasrc = ' ServerA '
exec sp_addlinkedsrvlogin
@rmtsrvname = ' DPVSERVER2 '
, @useself = ' false '
, @rmtuser = ' sa '
, @rmtpassword = ' password '
go
exec sp_addlinkedserver
@server = ' DPVSERVER2 ' , @srvproduct = '' ,
@provider = ' SQLOLEDB ' , @datasrc = ' ServerA '
exec sp_addlinkedsrvlogin
@rmtsrvname = ' DPVSERVER2 '
, @useself = ' false '
, @rmtuser = ' sa '
, @rmtpassword = ' password '
go
在A服务器上设置,属性,
//
ServerA
Exec sp_serveroption ' DPVSERVER1 ' , ' lazy schema validation ' , ' true '
Exec sp_serveroption ' DPVSERVER1 ' , ' lazy schema validation ' , ' true '
B服务器上,设置属性,
//
Sever B
Exec sp_serveroption ' DPVSERVER2 ' , ' lazy schema validation ' , ' true '
Exec sp_serveroption ' DPVSERVER2 ' , ' lazy schema validation ' , ' true '
A服务器上创建 分区视图:
//
Server A:
Create view DPV_Customers As
Select * from Customers
Union all
Select * from DPVSERVER1.Pubs.dbo.Customers
Create view DPV_Customers As
Select * from Customers
Union all
Select * from DPVSERVER1.Pubs.dbo.Customers
B服务器也创建 分区视图:(注意视图,UNION ALL的顺序)
Create
view
DPV_Customers
As
Select * from DPVSERVER2.Pubs.dbo.Customers
UNION ALL
Select * from Customers
Select * from DPVSERVER2.Pubs.dbo.Customers
UNION ALL
Select * from Customers
下面使用DPV_CUSTOMERS,和实际物理表一样
执行的时候打开查询分析器的执行计划,可以看到,实际,会优化,实际应该读那一张,写那一样表。
set
xact_abort
on
INSERT INTO DPV_CUSTOMERS VALUES ( ' AAMAY ' , ' FUZHOU COMPANY ' , ' MARRY ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' CJOHN ' , ' XIMEN COMPANY ' , ' MARRY ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' SMITH ' , ' SHANGHAI COMPANY ' , ' TOM ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' YOUNG ' , ' FUJIAN COMPANY ' , ' JANE ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' GTOPP ' , ' BEJING COMPANY ' , ' TOM ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' QUILH ' , ' BEJING COMPANY ' , ' TOM ' )
// SELECT * FROM DPV_Customers order by customerid
// SELECT * FROM DPV_Customers WHERE CustomerID = ' QUILH '
INSERT INTO DPV_CUSTOMERS VALUES ( ' AAMAY ' , ' FUZHOU COMPANY ' , ' MARRY ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' CJOHN ' , ' XIMEN COMPANY ' , ' MARRY ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' SMITH ' , ' SHANGHAI COMPANY ' , ' TOM ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' YOUNG ' , ' FUJIAN COMPANY ' , ' JANE ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' GTOPP ' , ' BEJING COMPANY ' , ' TOM ' )
INSERT INTO DPV_CUSTOMERS VALUES ( ' QUILH ' , ' BEJING COMPANY ' , ' TOM ' )
// SELECT * FROM DPV_Customers order by customerid
// SELECT * FROM DPV_Customers WHERE CustomerID = ' QUILH '
这个功能很不错,不过也会有一些问题,
1,表不能有DEFAULT 约束
2,INSERT 要一次性查询所有列的值,没有值的用NULL
3,各个表个CHECK集合不能有重复,也不充许找不一个满足要求的表。
还在研究中。。。。。。。。。。。。。。。