變更資料表後導致view錯亂解決方式

2017-09-27

有時因為撈取資料的邏輯過於複雜, 或常會因想讓程式撈取資料方便,

這時常會使用view的方式, 省去串好多張資料表的時間~

雖然使用很方便, 但也有幾個小要點需要注意,

這次遇到了因為在基底資料表新增了幾個欄位後, 導致view的內容錯亂~ 這是什麼原因呢?

我們知道建立View 是一個虛擬的資料表, 真正使用到的資料表稱之為基底資料表~

因view 不會真正把這些基底資料表中的資料又額外儲存一份, 因此靠的是儲存metadata來映對資料, 所以當基底資料表有修改到 Table Schema 時, 因為metadata 不會跟著更新, 所以就會造成資料錯亂...

首先先建立以下兩個資料表並各塞入五筆資料~

--資料表1
CREATE TABLE [dbo].[DemoTable]
(
[id] int NOT NULL,
[name] varchar(50) NULL,
[email] varchar(50) NULL
)
GO
INSERT INTO dbo.DemoTable (id, name, email)
VALUES
(1, 'John', 'xx1@xx.com'),
(2, 'Mark', 'xx2@xx.com'),
(3, 'Mary', 'xx3@xx.com'),
(4, 'Sandy', 'xx4@xx.com'),
(5, 'Tony', 'xx5@xx.com')
GO

--資料表2
CREATE TABLE [dbo].[DemoTable2]
(
[id] int NOT NULL,
[title] varchar(50) NULL
)
GO
INSERT INTO dbo.DemoTable2 (id, title)
VALUES
(1, 'PM'),
(2, 'SA'),
(3, 'PG'),
(4, 'PG'),
(5, 'PG')
GO

再來建立一個View串連這兩張資料表, 並使用* 號來取出第一個資料表的所有的欄位

CREATE VIEW dbo.DemoView
AS
SELECT T1.*, T2.title
FROM dbo.DemoTable AS T1
LEFT JOIN dbo.DemoTable2 AS T2 ON T1.id = T2.id
GO

SELECT * FROM DemoView

資料呈現如上圖~

接著來做變更基底資料表, 將資料表1新增一個欄位~

ALTER TABLE dbo.DemoTable ADD [address] varchar(50) NULL;
SELECT * FROM dbo.DemoTable

這時再來看看view有什麼變化~

SELECT * FROM DemoView

發現到了嗎? 最後一欄的title 原本是串資料表2的資料, 會顯示職稱的資料, 但因為在資料表1新增了欄位後, 導致資料錯亂了... 應該有值的卻不見了~

這種問題並不太好發現, 等到系統開始出現問題, 可能根本也沒想到會是因為只是在某張資料表新增或是異動欄位造成的~

因此在使用view 時可以有以下幾個注意事項:

1. 在select資料時最好不要使用 * 號, 而是將所需要用到的欄位名稱一一打出來~

CREATE VIEW dbo.DemoView
AS
SELECT T1.id, T1.name, T1.email , T2.title --乖乖打出所需要的欄位名稱
FROM dbo.DemoTable AS T1
LEFT JOIN dbo.DemoTable2 AS T2 ON T1.id = T2.id
GO

2. 利用 WITH SCHEMABINDING 選項來建立view, 防止變更基底資料表~

CREATE VIEW dbo.DemoView
WITH SCHEMABINDING  
AS
SELECT T1.id, T1.name, T1.email , T2.title
FROM dbo.DemoTable AS T1
LEFT JOIN dbo.DemoTable2 AS T2 ON T1.id = T2.id
GO

在加了WITH SCHEMABINDING 後, 基本上在寫 SELECT T1.* 時就會無法建立view而得到以下錯誤
訊息 1054，層級 15，狀態 7，程序 DemoView，行 4
結構描述繫結的物件中不允許使用 '*' 語法。

加了WITH SCHEMABINDING 後, 當要變更基底資料表時也會顯示訊息:

EXECUTE sp_rename N'dbo.DemoTable.email', N'test', 'COLUMN' --將email欄位重新命名為text

訊息 15336，層級 16，狀態 1，程序 sp_rename，行 497
由於物件參與強制相依性，所以物件 'dbo.DemoTable.email' 無法重新命名。

3. 在做基底資料表 Table Schema 變更時, 也可以使用sp_refreshview 來找出需要重建的view 做更新一下~

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' 
FROM sys.objects so INNER JOIN sys.sql_dependencies sd 
ON so.object_id = sd.object_id 
WHERE type = 'V' 
AND sd.referenced_major_id = object_id('更新的TableName');

接著只要將內容拷出來並執行即可更新view了~

回首頁