一、sql2000分步式分区视图,SQL 2005方法同样,只是将其优化性能--ServerAUse studygo--创建测试表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'))
--select * from customers--Server B use studygoCreate 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--ServerA--在serverA服务器上远程连接数据库服务器Bexec sp_addlinkedserver @server='DPVSERVER1', @srvproduct='', @provider='SQLOLEDB', @datasrc='ServerB'goexec sp_addlinkedsrvlogin @rmtsrvname = 'DPVSERVER1' , @useself = 'false' , @rmtuser = 'sa' , @rmtpassword = 'password' go--测试是服务器B否可以连接上select top 10 * from DPVSERVER1.[study].dbo.customers--ServerB--在serverB服务器上远程连接数据库服务器Aexec sp_addlinkedserver @server='DPVSERVER2', @srvproduct='', @provider='SQLOLEDB', @datasrc='ServerA'exec sp_addlinkedsrvlogin @rmtsrvname = 'DPVSERVER2' , @useself = 'false' , @rmtuser = 'sa' , @rmtpassword = 'password' go--测试是服务器A否可以连接上select top 10 * from DPVSERVER2.[study].dbo.customers
--确定是否检查远程表的架构。如果为 ture，则在查询开始时跳过远程表的架构检查,可以优化查询。--ServerAExec sp_serveroption 'GLOBE01', 'lazy schema validation', 'true'--Sever BExec sp_serveroption 'SO', 'lazy schema validation', 'true'
--创建分步式视图--Server A： Create view DPV_Customers As Select * from Customers Union all Select * from [192.168.42.37].study.dbo.Customers--Server B Create view DPV_Customers As Select * from DPVSERVER2.Pubs.dbo.Customers UNION ALL Select * from Customers
--select * from DPV_Customers
set xact_abort on--这个不能少。。是必须要，因为是远程连接/*当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。 当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。*/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 --通过约束访问符合其约束上的表，在此只访问Server B ,因为它符合其约束SELECT * FROM DPV_Customers WHERE CustomerID= 'QUILH'--通过约束访问符合其约束上的表，在此只访问Server A ,因为它符合其约束SELECT * FROM DPV_Customers WHERE CustomerID= 'AAMAY'
=================================================================================
二、sql2005分区表--添加文件组alter database Study add filegroup [fg1]goalter database Study add filegroup [fg2]goalter database Study add filegroup [fg3]go--在各自文件组中添加数据各自存储文件ndf,并放到不同的盘里alter database Studyadd file(name='fg1', filename='c:/fg1.ndf',size=5mb)to filegroup [fg1]goalter database Studyadd file(name='fg2', filename='d:/fg2.ndf',size=5mb)to filegroup [fg2]goalter database Studyadd file(name='fg3', filename='e:/fg3.ndf',size=5mb)to filegroup [fg3]gouse Studygo--创建分区函数，水平左分区Create partition function emailPF(nvarchar(50)) as range right for values ('G','N')go--在文件组fg1,fg2,fg3上创建分区架构Create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)go--在分区架构上创建表Create table customermail (custid int, email nvarchar(50)) on emailPS(email)go--测试效率insert into customermail(custid,email)select 1,'A'union all select 2,'AA' union all select 3,'B' union all select 2,'BB'union all select 3,'CC' union all select 5,'C' union all select 44,'DD'union all select 23,'D' union all select 5,'EE' union all select 121,'FF'union all select 12,'G' union all select 2,'H' union all select 33,'I'union all select 121,'J' union all select 5,'K' union all select 5,'KK'union all select 11,'L' union all select 5,'M' union all select 22,'N'union all select 5,'O' union all select 5,'W' union all select 5,'Q'union all select 5,'W' union all select 5,'R' union all select 5,'S'union all select 5,'W' union all select 5,'T' union all select 5,'U'union all select 5,'W' union all select 5,'T' union all select 5,'V'union all select 5,'W' union all select 5,'T' union all select 5,'x'union all select 5,'T' union all select 5,'y' union all select 5,'T'union all select 5,'z'--查询执行计划用到了分区查询select * from customermail WHERE email<'F'select * from customermail WHERE custid=5 and email<'F'select * from customermail WHERE email <'H'select * from customermail WHERE email between 'H' and 'K'select * from customermail WHERE email<'W'
