sql view

//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')
)

//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
//ServerA
exec  sp_addlinkedserver   
       @server='DPVSERVER1', @srvproduct='',
       @provider='SQLOLEDB', @datasrc='ServerB'
go
exec  sp_addlinkedsrvlogin
 @rmtsrvname =  'DPVSERVER1'
     , @useself =  'false'
     , @rmtuser =  'sa'
     , @rmtpassword = 'password'
go
//ServerB
exec  sp_addlinkedserver   
       @server='DPVSERVER2', @srvproduct='',
       @provider='SQLOLEDB', @datasrc='ServerA'
exec  sp_addlinkedsrvlogin
 @rmtsrvname =  'DPVSERVER2'
     , @useself =  'false'
     , @rmtuser =  'sa'
     , @rmtpassword = 'password'
go
//ServerA
Exec sp_serveroption 'DPVSERVER1', 'lazy schema validation', 'true'
//Sever B
Exec sp_serveroption 'DPVSERVER2', 'lazy schema validation', 'true'

//Server A：
  Create view DPV_Customers  As
   Select *  from Customers
   Union all
   Select *  from  DPVSERVER1.Pubs.dbo.Customers
//Server B
   Create view DPV_Customers  As
   Select *  from  DPVSERVER2.Pubs.dbo.Customers
   UNION ALL
   Select *  from 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'

 

 sql2005:

 

alter database adventureWorks add filegroup [fg1]
go
alter database adventureWorks add filegroup [fg2]
go
alter database adventureWorks add filegroup [fg3]
go
alter database adventureWorks
add file
(name='fg1',
 filename='c:\fg1.ndf',
size=5mb)
to filegroup [fg1]
go
alter database adventureWorks
add file
(name='fg2',
 filename='d:\fg2.ndf',
size=5mb)
to filegroup [fg2]
go
alter database adventureWorks
add file
(name='fg3',
 filename='e:\fg3.ndf',
size=5mb)
to filegroup [fg3]
go
use adventureWorks
go
Create partition function emailPF(nvarchar(50)) as range right for values ('G','N')
go
Create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)
go
Create table customermail (custid int, email nvarchar(50)) on emailPS(email)
go