SqlServer 垂直分表(减少步伐改动)

由于sqlserver的设计特殊性，一般大量数据一般都是采用水平分表，而垂直分表只是把text、图片都较大数据放到单独的表中，这样数据设计会更合理，相对于mysql可能要好一点，mssql本来就是一个文件，基本上提升不大，目前来看几十万的数据没有分不分表没有任何影响，对于千万以上数据还是采用水平分表比较好。
复制代码

--  源表
CREATE TABLE [dbo].[DemoTab](
[Guid] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](30) NOT NULL,
[Password] [nvarchar](30) NOT NULL,
[UserAccount] [varchar](30) NOT NULL,
[Amount] [numeric](18, 4) NULL,
CONSTRAINT [PK_DemoTab] PRIMARY KEY CLUSTERED ([Guid])
)
GO
 
 
ALTER TABLE [dbo].[DemoTab] 
ADD CONSTRAINT [DF_DemoTab_Guid] DEFAULT (newsequentialid()) FOR [Guid]
GO
 
--  原来是访问视图的（好处就是视图层不变）
CREATE VIEW [dbo].[VDemoTab]
AS
SELECT [Guid],[UserName],[Password],[UserAccount],[Amount]
FROM [dbo].[DemoTab]
GO
复制代码

--  分表【1】,以该表为"主表"，其他拆分出的表为"子表"
CREATE TABLE [dbo].[DemoTab001](
[Guid] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](30) NOT NULL,
[Password] [nvarchar](30) NOT NULL,
CONSTRAINT [PK_DemoTab001] PRIMARY KEY CLUSTERED ([Guid])
)
GO
 
--  主键默认值可以不需要,因为插入数据前需要确定主键值
--ALTER TABLE [dbo].[DemoTab001] 
--ADD CONSTRAINT [DF_DemoTab001_Guid] DEFAULT (newsequentialid()) FOR [Guid]
--GO
 
--  分表【2】,"子表"
CREATE TABLE [dbo].[DemoTab002](
[Guid] [uniqueidentifier] NOT NULL,
[UserAccount] [varchar](30) NOT NULL,
[Amount] [numeric](18, 4) NULL,
CONSTRAINT [PK_DemoTab002] PRIMARY KEY CLUSTERED ([Guid])
)
GO
 
--  主键默认值可以不需要,因为插入数据前需要确定主键值
--ALTER TABLE [dbo].[DemoTab002] 
--ADD CONSTRAINT [DF_DemoTab002_Guid] DEFAULT (newsequentialid()) FOR [Guid]
--GO
 
 
--  若主表变更主键则级联更新或删除（主键通常是不更新的，也可省去 ON UPDATE CASCADE）
ALTER TABLE [dbo].[DemoTab002] 
ADD CONSTRAINT [FK_DemoTab002_DemoTab001_Guid] FOREIGN KEY ([Guid]) 
REFERENCES [DemoTab001]([Guid]) ON UPDATE CASCADE ON DELETE CASCADE
GO
复制代码

--  拆分后使用联合视图(INNER JOIN 也可以)
ALTER VIEW [dbo].[VDemoTab]
AS
SELECT T1.[Guid],T1.[UserName],T1.[Password],T2.[UserAccount],T2.[Amount]
FROM [dbo].[DemoTab001] T1 LEFT JOIN [dbo].[DemoTab002] T2 ON T1.[Guid]=T2.[Guid]
GO
复制代码

--  insert 触发器
CREATE TRIGGER [dbo].[tgr_VDemoTab_insert]
ON [dbo].[VDemoTab] 
INSTEAD OF INSERT
AS 
BEGIN
 INSERT INTO [dbo].[DemoTab001]([Guid],[UserName],[Password])
 SELECT [Guid],[UserName],[Password] FROM inserted;
 
 INSERT INTO [dbo].[DemoTab002]([Guid],[UserAccount],[Amount])
 SELECT [Guid],[UserAccount],[Amount] FROM inserted;
END
GO
复制代码

--  update 触发器
CREATE TRIGGER [dbo].[tgr_VDemoTab_update]  
ON [dbo].[VDemoTab]   
INSTEAD OF UPDATE  
AS
BEGIN
 UPDATE T1 SET 
 T1.[UserName] = T2.[UserName], 
 T1.[Password] = T2.[Password]
 FROM [dbo].[DemoTab001] AS T1, inserted AS T2 WHERE T1.[Guid] = T2.[Guid] 
 
 UPDATE T1 SET 
 T1.[UserAccount] = T2.[UserAccount], 
 T1.[Amount] = T2.[Amount]
 FROM [dbo].[DemoTab002] AS T1, inserted AS T2 WHERE T1.[Guid] = T2.[Guid] 
END
GO
复制代码

--  delete 触发器
CREATE TRIGGER [dbo].[tgr_VDemoTab_delete]  
ON [dbo].[VDemoTab]   
INSTEAD OF DELETE  
AS
BEGIN
    DELETE FROM [dbo].[DemoTab001]
    WHERE [Guid] IN (SELECT [Guid] FROM deleted)
END
GO
复制代码

INSERT INTO [dbo].[VDemoTab]([Guid],[UserName],[Password],[UserAccount],[Amount])
SELECT NEWID(),'user01','pw01','account01',100
UNION ALL
SELECT NEWID(),'user02','pw02','account02',99
UNION ALL
SELECT NEWID(),'user03','pw03','account03',0
GO
 
UPDATE [VDemoTab] SET [Password]='pw',[Amount]='10'
WHERE [Amount] >=0 AND [Amount]<100 AND [UserName] LIKE '%3'
GO
 
DELETE FROM [VDemoTab] WHERE [UserName] = 'user03'
GO
 
SELECT * FROM [dbo].[DemoTab001] 
SELECT * FROM [dbo].[DemoTab002] 
SELECT * FROM [dbo].[VDemoTab]
复制代码