使用SqlServer怎么實現(xiàn)垂直分表

使用SqlServer怎么實現(xiàn)垂直分表？很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

源表設計結(jié)構(gòu)：

--  源表
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

注：拆分后各表的主鍵都是相同了，而且拆分后的表是規(guī)范化的。

現(xiàn)在拆成兩張表：

注意選擇一張表作為基表，其他表都有與該表的外鍵。

--  分表【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
 
--  主鍵默認值可以不需要,因為插入數(shù)據(jù)前需要確定主鍵值
--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
 
--  主鍵默認值可以不需要,因為插入數(shù)據(jù)前需要確定主鍵值
--ALTER TABLE [dbo].[DemoTab002] 
--ADD CONSTRAINT [DF_DemoTab002_Guid] DEFAULT (newsequentialid()) FOR [Guid]
--GO
 
 
--  若主表變更主鍵則級聯(lián)更新或刪除（主鍵通常是不更新的，也可省去 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

如果之前是對單個表或者視圖操作，拆分之后邏輯層改動可能很多，為保持改動最小，可以用聯(lián)合視圖操作。怎么連接表依個人情況而定。

--  拆分后使用聯(lián)合視圖(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

這時問題來了，要對表進行DML操作，insert , update , delete 怎么解決？因為要求主鍵是分散在多個表并且是相同的！

這時只能用考慮觸發(fā)器來保證一致性了，觸發(fā)器則定義在視圖上，使用的是 INSTEAD OF 類型的觸發(fā)器。

insert 觸發(fā)器：

視圖 [VDemoTab] 中的 [Guid] 為表 插入時值，在插入觸發(fā)器中，虛擬表[inserted]的[Guid]是唯一的，所以在觸發(fā)器中可以同時使用該 [Guid] 插入到多個分表中，保證了多個分表的[Guid]是相同的！

--  insert 觸發(fā)器
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 觸發(fā)器：

同理，更新時涉及虛擬表 deleted 和 inserted，而更新是對視圖[VDemoTab]更新的，所以虛擬表inserted包括了所有的字段，所以需要觸發(fā)器分別更新多個分表。

--  update 觸發(fā)器
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 觸發(fā)器：

刪除視圖[VDemoTab]記錄，涉及多個表則不允許刪除，因此只要刪除"主表"的記錄即可，其他分表都會級聯(lián)刪除。

--  delete 觸發(fā)器
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

設計基本就完成了，現(xiàn)在進行測試。

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]

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業(yè)資訊頻道，感謝您對億速云的支持。