分区视图:分区视图是通过对成员表使用union all所定义的视图
这些成员的结构相同，一般情况下，为分区视图具有如下语法： create view vie_name with schemabinding as select <select_list1> from t1 union allselect <select_list2>
from t2 union all .. select <select_listn> from tn 分区视图的作用：用在一台或多台服务器水平连接一组成员表中的分区数据 使数据看起来就像来自一个表
CREATE TABLE China_student_qinghua -- 清华大学学生信息表
( sc_no INT NOT NULL CHECK(sc_no=1), -- 清华大学的编号规定为1 sc_name varchar(50) NOT NULL CHECK(sc_name='清华大学'), s_no char(8), s_name char(8) NOT NULL, s_sex char(2) CHECK(s_sex = '男' OR s_sex = '女'), s_birthday smalldatetime CHECK(s_birthday>='1970-1-1' AND s_birthday<='2000-1-1'), s_speciality varchar(50) DEFAULT '计算机软件与理论', s_avgrade numeric(3,1) CHECK(s_avgrade >= 0 AND s_avgrade <= 100), s_dept varchar(50) DEFAULT '计算机科学系', CONSTRAINT sc_no_and_s_no1 PRIMARY KEY(sc_no, s_no) -- 定义主键约束 );CREATE TABLE China_student_zhejiang_university -- 浙江大学学生信息表
( sc_no INT NOT NULL CHECK(sc_no=2), -- 浙江大学的编号规定为 sc_name varchar(50) NOT NULL CHECK(sc_name ='浙江大学'), s_no char(8), s_name char(8) NOT NULL, s_sex char(2) CHECK(s_sex = '男' OR s_sex = '女'), s_birthday smalldatetime CHECK(s_birthday>='1970-1-1' AND s_birthday<='2000-1-1'), s_speciality varchar(50) DEFAULT '计算机软件与理论', s_avgrade numeric(3,1) CHECK(s_avgrade >= 0 AND s_avgrade <= 100), s_dept varchar(50) DEFAULT '计算机科学系', CONSTRAINT sc_no_and_s_no2 PRIMARY KEY(sc_no, s_no) -- 定义主键约束 );CREATE TABLE China_student_wuhan_university -- 武汉大学学生信息表
( sc_no INT NOT NULL CHECK(sc_no=3), -- 武汉大学的编号规定为 sc_name varchar(50) NOT NULL CHECK(sc_name ='武汉大学'), s_no char(8), s_name char(8) NOT NULL, s_sex char(2) CHECK(s_sex = '男' OR s_sex = '女'), s_birthday smalldatetime CHECK(s_birthday>='1970-1-1' AND s_birthday<='2000-1-1'), s_speciality varchar(50) DEFAULT '计算机软件与理论', s_avgrade numeric(3,1) CHECK(s_avgrade >= 0 AND s_avgrade <= 100), s_dept varchar(50) DEFAULT '计算机科学系', CONSTRAINT sc_no_and_s_no3 PRIMARY KEY(sc_no, s_no) -- 定义主键约束 );
CREATE VIEW China_student_view
AS SELECT * FROM MyDatabase.dbo.China_student_qinghua UNION ALL SELECT * FROM MyDatabase.dbo.China_student_zhejiang_university UNION ALL SELECT * FROM MyDatabase.dbo.China_student_wuhan_university;
-- 在Server1上创建的分布式分区视图
CREATE VIEW China_student_view AS SELECT * FROM MyDatabase.dbo.China_student_qinghua UNION ALL SELECT * FROM Server2.MyDatabase.dbo.China_student_zhejiang_university UNION ALL SELECT * FROM Server3.MyDatabase.dbo.China_student_wuhan_university; 在多个服务器中的数据表 --------------------------------------------------------------------------------------【说明】部分的代码：
-- 创建本地分区视图 CREATE VIEW China_student_view AS SELECT * FROM MyDatabase.dbo.China_student_qinghua UNION ALL SELECT * FROM MyDatabase.dbo.China_student_zhejiang_university UNION ALL SELECT * FROM MyDatabase.dbo.China_student_wuhan_university; --【例9.24】 INSERT INTO China_student_view VALUES(1,'清华大学','20060201','李好','男', '1987-1-1', '计算机应用技术', 94.5, '计算机系'); INSERT INTO China_student_view VALUES(1,'清华大学','20060203','王智高','男', '1986-12-25', '网络工程', 85.8, '信息工程系'); INSERT INTO China_student_view VALUES(2,'浙江大学','20060204','赵刚','男', '1988-7-1', '网络工程', 77.8, '信息工程系'); INSERT INTO China_student_view VALUES(2,'浙江大学','20060205','贾志','男', '1985-9-18', '计算机应用技术', 45.0, '计算机系'); INSERT INTO China_student_view VALUES(3,'武汉大学','20060206','丽思','女', '1984-8-1', '计算机应用技术', 61.3, '计算机系'); INSERT INTO China_student_view VALUES(3,'武汉大学','20060207','赵智远','男', '1983-11-2', '电子商务', 72.8, '电子商务系');-----------------------------------------------------------------
SELECT * FROM China_student_view;
-----------------------------------------------------------------
SELECT * FROM China_student_qinghua;
SELECT * FROM China_student_zhejiang_university; SELECT * FROM China_student_wuhan_university;