配置SQL SERVER垂直分片
SQL SERVER垂直分片
垂直分片比水平分片要复杂一下,主要是写触发器,我头都大了,不多解释了,直接贴代码:
A:

Code
1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 EXECUTE sp_serveroption @server='RENHU\A',@optname='lazy schema validation',@optvalue='true'
9 GO
10
11 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
12 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
13 GO
14
15 CREATE DATABASE MEDIA;
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到B
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\B')
23 EXECUTE sp_addlinkedserver 'RENHU\B', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\B','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY IDENTITY(1,1),
31 [USER_NAME] NVARCHAR(20) NOT NULL,
32 [USER_PWD] VARBINARY NOT NULL,
33 [USER_ROLE] INT NOT NULL); -- 类别,0代表管理员,1代表普通用户
34 GO
35
36 -- 建立用户
37 CREATE USER XQLS FROM LOGIN xqls;
38 GO
39
40 -- 分配权限
41 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
42 GO
43
44 -- 建立视图
45 CREATE VIEW [USER_VIEW]
46 AS
47 SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
48 A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
49 B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
50 B.[USER_GENDER] AS [USER_GENDER], B.[USER_DES] AS [USER_DES]
51 FROM MEDIA.dbo.[USER] A, [RENHU\B].MEDIA.dbo.[USER] B
52 WHERE A.[USER_ID] = B.[USER_ID];
53 GO
54
55 -- 为视图建立插入触发器
56 CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
57 INSTEAD OF INSERT
58
59 AS
60
61 BEGIN TRANSACTION
62 INSERT INTO MEDIA.dbo.[USER]
63 SELECT [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
64 INSERT INTO [RENHU\B].MEDIA.dbo.[USER]
65 SELECT scope_identity(), [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
66 IF ( @@ERROR <> 0 )
67 BEGIN
68 ROLLBACK TRANSACTION
69 END
70 ELSE
71 COMMIT TRANSACTION
72
73 GO
74
75 -- 为视图建立更新触发器
76 CREATE TRIGGER [TRG_UPDATE_USER] on [USER_VIEW]
77 INSTEAD OF UPDATE
78 AS
79 BEGIN TRANSACTION
80 IF UPDATE([USER_ID])
81 BEGIN
82 RAISERROR(N'USER_ID列不能被更新', 10, 1);
83 ROLLBACK TRANSACTION
84 RETURN
85 END
86
87 IF UPDATE([USER_NAME])
88 BEGIN
89 RAISERROR(N'USER_NAME列不能被更新', 10, 1);
90 ROLLBACK TRANSACTION
91 RETURN
92 END
93
94 IF UPDATE([USER_PWD])
95 BEGIN
96 UPDATE [USER] SET [USER_PWD] = Inserted.[USER_PWD] FROM [USER] JOIN Inserted
97 ON [USER].[USER_ID] = Inserted.[USER_ID];
98 END
99
100 IF UPDATE([USER_ROLE])
101 BEGIN
102 UPDATE [USER] SET [USER_ROLE] = Inserted.[USER_ROLE] FROM [USER] JOIN Inserted
103 ON [USER].[USER_ID] = Inserted.[USER_ID];
104 END
105
106 IF UPDATE([USER_IM])
107 BEGIN
108 UPDATE A SET [USER_IM] = Inserted.[USER_IM] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
109 ON A.[USER_ID] = Inserted.[USER_ID];
110 END
111
112 IF UPDATE([USER_AGE])
113 BEGIN
114 UPDATE A SET [USER_AGE] = Inserted.[USER_AGE] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
115 ON A.[USER_ID] = Inserted.[USER_ID];
116 END
117
118 IF UPDATE([USER_GENDER])
119 BEGIN
120 UPDATE A SET [USER_GENDER] = Inserted.[USER_GENDER] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
121 ON A.[USER_ID] = Inserted.[USER_ID];
122 END
123
124 IF UPDATE([USER_DES])
125 BEGIN
126 UPDATE A SET [USER_DES] = Inserted.[USER_DES] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
127 ON A.[USER_ID] = Inserted.[USER_ID];
128 END
129
130 COMMIT TRANSACTION
131
132 --insert into [USER_VIEW]([USER_NAME],[USER_PWD],[USER_ROLE],[USER_IM],[USER_AGE],[USER_GENDER],[USER_DES]) values('h',12,0,'123456',22,0,'I am
');
133 --
134 --update [USER_VIEW] SET [USER_IM] = '45698', [USER_PWD] = 2356, [USER_ROLE]=10 where [USER_ID] = 3;
B:

Code
1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 CREATE DATABASE MEDIA;
9 GO
10
11 EXECUTE sp_serveroption @server='RENHU\B',@optname='lazy schema validation',@optvalue='true'
12 GO
13
14 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
15 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到A
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\A')
23 EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY,
31 [USER_IM] NVARCHAR(50), -- QQ/MSN
32 [USER_AGE] INT, -- 年龄
33 [USER_GENDER] BIT, -- 性别
34 [USER_DES] NTEXT) -- 个人简介
35 GO
36
37 -- 建立用户
38 CREATE USER XQLS FROM LOGIN xqls;
39 GO
40
41 -- 分配权限
42 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
43 GO
44
45 ---- 建立视图
46 --CREATE VIEW [USER_VIEW]
47 --AS
48 -- SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
49 -- A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
50 -- B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
51 -- B.[USER_GENDER] AS [USER_GENDER],B.[USER_DES] AS [USER_DES]
52 -- FROM [RENHU\A].MEDIA.dbo.[USER] A, MEDIA.dbo.[USER] B
53 -- WHERE A.[USER_ID] = B.[USER_ID];
54 --GO
55 --
56 ---- 为视图建立插入触发器
57 --CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
58 --INSTEAD OF INSERT
59 --
60 --AS
61 --
62 --BEGIN TRANSACTION
63 -- INSERT INTO MEDIA.dbo.[USER]
64 -- SELECT [USER_ID], [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
65 -- INSERT INTO [RENHU\A].MEDIA.dbo.[USER]
66 -- SELECT [USER_ID], [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
67 --IF ( @@ERROR <> 0 )
68 --BEGIN
69 -- ROLLBACK TRANSACTION
70 --END
71 --ELSE
72 -- COMMIT TRANSACTION
73 --GO
A:


1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 EXECUTE sp_serveroption @server='RENHU\A',@optname='lazy schema validation',@optvalue='true'
9 GO
10
11 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
12 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
13 GO
14
15 CREATE DATABASE MEDIA;
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到B
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\B')
23 EXECUTE sp_addlinkedserver 'RENHU\B', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\B','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY IDENTITY(1,1),
31 [USER_NAME] NVARCHAR(20) NOT NULL,
32 [USER_PWD] VARBINARY NOT NULL,
33 [USER_ROLE] INT NOT NULL); -- 类别,0代表管理员,1代表普通用户
34 GO
35
36 -- 建立用户
37 CREATE USER XQLS FROM LOGIN xqls;
38 GO
39
40 -- 分配权限
41 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
42 GO
43
44 -- 建立视图
45 CREATE VIEW [USER_VIEW]
46 AS
47 SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
48 A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
49 B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
50 B.[USER_GENDER] AS [USER_GENDER], B.[USER_DES] AS [USER_DES]
51 FROM MEDIA.dbo.[USER] A, [RENHU\B].MEDIA.dbo.[USER] B
52 WHERE A.[USER_ID] = B.[USER_ID];
53 GO
54
55 -- 为视图建立插入触发器
56 CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
57 INSTEAD OF INSERT
58
59 AS
60
61 BEGIN TRANSACTION
62 INSERT INTO MEDIA.dbo.[USER]
63 SELECT [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
64 INSERT INTO [RENHU\B].MEDIA.dbo.[USER]
65 SELECT scope_identity(), [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
66 IF ( @@ERROR <> 0 )
67 BEGIN
68 ROLLBACK TRANSACTION
69 END
70 ELSE
71 COMMIT TRANSACTION
72
73 GO
74
75 -- 为视图建立更新触发器
76 CREATE TRIGGER [TRG_UPDATE_USER] on [USER_VIEW]
77 INSTEAD OF UPDATE
78 AS
79 BEGIN TRANSACTION
80 IF UPDATE([USER_ID])
81 BEGIN
82 RAISERROR(N'USER_ID列不能被更新', 10, 1);
83 ROLLBACK TRANSACTION
84 RETURN
85 END
86
87 IF UPDATE([USER_NAME])
88 BEGIN
89 RAISERROR(N'USER_NAME列不能被更新', 10, 1);
90 ROLLBACK TRANSACTION
91 RETURN
92 END
93
94 IF UPDATE([USER_PWD])
95 BEGIN
96 UPDATE [USER] SET [USER_PWD] = Inserted.[USER_PWD] FROM [USER] JOIN Inserted
97 ON [USER].[USER_ID] = Inserted.[USER_ID];
98 END
99
100 IF UPDATE([USER_ROLE])
101 BEGIN
102 UPDATE [USER] SET [USER_ROLE] = Inserted.[USER_ROLE] FROM [USER] JOIN Inserted
103 ON [USER].[USER_ID] = Inserted.[USER_ID];
104 END
105
106 IF UPDATE([USER_IM])
107 BEGIN
108 UPDATE A SET [USER_IM] = Inserted.[USER_IM] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
109 ON A.[USER_ID] = Inserted.[USER_ID];
110 END
111
112 IF UPDATE([USER_AGE])
113 BEGIN
114 UPDATE A SET [USER_AGE] = Inserted.[USER_AGE] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
115 ON A.[USER_ID] = Inserted.[USER_ID];
116 END
117
118 IF UPDATE([USER_GENDER])
119 BEGIN
120 UPDATE A SET [USER_GENDER] = Inserted.[USER_GENDER] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
121 ON A.[USER_ID] = Inserted.[USER_ID];
122 END
123
124 IF UPDATE([USER_DES])
125 BEGIN
126 UPDATE A SET [USER_DES] = Inserted.[USER_DES] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
127 ON A.[USER_ID] = Inserted.[USER_ID];
128 END
129
130 COMMIT TRANSACTION
131
132 --insert into [USER_VIEW]([USER_NAME],[USER_PWD],[USER_ROLE],[USER_IM],[USER_AGE],[USER_GENDER],[USER_DES]) values('h',12,0,'123456',22,0,'I am

133 --
134 --update [USER_VIEW] SET [USER_IM] = '45698', [USER_PWD] = 2356, [USER_ROLE]=10 where [USER_ID] = 3;
B:


1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 CREATE DATABASE MEDIA;
9 GO
10
11 EXECUTE sp_serveroption @server='RENHU\B',@optname='lazy schema validation',@optvalue='true'
12 GO
13
14 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
15 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到A
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\A')
23 EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY,
31 [USER_IM] NVARCHAR(50), -- QQ/MSN
32 [USER_AGE] INT, -- 年龄
33 [USER_GENDER] BIT, -- 性别
34 [USER_DES] NTEXT) -- 个人简介
35 GO
36
37 -- 建立用户
38 CREATE USER XQLS FROM LOGIN xqls;
39 GO
40
41 -- 分配权限
42 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
43 GO
44
45 ---- 建立视图
46 --CREATE VIEW [USER_VIEW]
47 --AS
48 -- SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
49 -- A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
50 -- B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
51 -- B.[USER_GENDER] AS [USER_GENDER],B.[USER_DES] AS [USER_DES]
52 -- FROM [RENHU\A].MEDIA.dbo.[USER] A, MEDIA.dbo.[USER] B
53 -- WHERE A.[USER_ID] = B.[USER_ID];
54 --GO
55 --
56 ---- 为视图建立插入触发器
57 --CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
58 --INSTEAD OF INSERT
59 --
60 --AS
61 --
62 --BEGIN TRANSACTION
63 -- INSERT INTO MEDIA.dbo.[USER]
64 -- SELECT [USER_ID], [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
65 -- INSERT INTO [RENHU\A].MEDIA.dbo.[USER]
66 -- SELECT [USER_ID], [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
67 --IF ( @@ERROR <> 0 )
68 --BEGIN
69 -- ROLLBACK TRANSACTION
70 --END
71 --ELSE
72 -- COMMIT TRANSACTION
73 --GO

本文基于署名 2.5 中国大陆许可协议发布,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名小橋流水(包含链接)。如您有任何疑问或者授权方面的协商,请给我发邮件。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 周边上新:园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源!
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· [AI/GPT/综述] AI Agent的设计模式综述