配置SQL SERVER 2005 分布式水平分片
2009-10-08 00:00:00 来源:WEB开发网Server CHINA-E931FACA9:
1CREATE DATABASE SalesDB;
2EXECUTE sp_serveroption @server='CHINA-E931FACA9\B',@optname='lazy schema validation',@optvalue='true'
3CREATE LOGIN xqls WITH Password = 'wisdom317'
4
5GO
6
7USE SalesDB
8CREATE USER xqls FROM LOGIN xqls
9
10GO
11
12-- 连接到A
13EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
14EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
15
16GO
17
18-- 创建表结构
19IF OBJECT_ID('SalesHistory','U') > 0
20
21DROP TABLE SalesHistory
22
23GO
24
25CREATE TABLE SalesHistory
26
27(
28 SaleID INT PRIMARY KEY,
29 Product VARCHAR(30) NOT NULL,
30 SaleDate DATETIME,
31 SalePrice MONEY,
32 Region VARCHAR(5) NOT NULL,
33 CONSTRAINT chk_Region CHECK (SaleID >=20000)
34
35)
36
37GO
38
39
40-- 添加测试数据
41DECLARE @i SMALLINT, @Region VARCHAR(5)
42
43SET @i = 20000
44
45SET @Region = 'East'
46
47
48
49WHILE (@i <=26000)
50
51BEGIN
52 INSERT INTO SalesHistory
53 (SaleID, Product, SaleDate, SalePrice, Region)
54 VALUES
55 (@i, 'Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57), @Region )
56 SET @i = @i + 1
57 INSERT INTO SalesHistory
58 (SaleID, Product, SaleDate, SalePrice, Region)
59 VALUES
60 (@i, 'BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13), @Region)
61 SET @i = @i + 1
62 INSERT INTO SalesHistory
63 (SaleID, Product, SaleDate, SalePrice, Region)
64 VALUES
65 (@i, 'PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29), @Region )
66 SET @i = @i + 1
67
68END
69
70-- 分配用户权限
71GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls
72
73GO
74
75-- 建立视图
76CREATE VIEW dpv_SalesHistory
77
78AS
79 SELECT
80 SaleID, Product, Region, SaleDate, SalePrice
81 FROM SalesDB.dbo.SalesHistory
82 UNION ALL
83 SELECT
84 SaleID, Product, Region, SaleDate, SalePrice
85 FROM [RENHU\A].SalesDB.dbo.SalesHistory
86
87GO
88
89-- 测试视图
90select * from dpv_SalesHistory
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接