SQL Server 視圖是否會根據為其提供的表自動更新其數據類型？

有人錯誤地在數據庫表中創建了一堆 nchar 欄位。我注意到了這一點，在檢查了這個問題之後，我們將這些欄位移動到 nvarchar 類型並修剪裡面的值。

在我的腳本中，我目前僅限於提取具有 nchar 類型的表，假設由表提供的視圖將知道更新它們的值。這個假設是否正確，或者我也應該在這個列表中包含視圖？

作為參考，目前形式的腳本如下（主要取自這個偉大的 SO 答案）：

declare @tn nvarchar(128)
declare @cn nvarchar(128)
declare @ln int

declare @sql as nvarchar(1000)

declare c cursor for 
   select cols.table_name,cols.column_name,cols.character_maximum_length 
   from information_schema.columns cols
       inner join information_schema.tables tabs 
           on (cols.TABLE_SCHEMA = tabs.TABLE_SCHEMA and cols.TABLE_NAME = tabs.TABLE_NAME)
   where cols.data_type ='nchar' and tabs.TABLE_TYPE = 'BASE TABLE' 

open c
fetch next from c into @tn, @cn, @ln

while @@FETCH_STATUS = 0
begin

   set @sql = 'alter table ' + @tn + ' alter column ' 
       + @cn + ' nvarchar(' + convert(nvarchar(50), @ln) + ')'
   exec sp_executesql @sql

   set @sql = 'update ' + @tn + ' set ' + @cn + ' = LTRIM(RTRIM(' + @cn + '))'
   exec sp_executesql @sql

   fetch next from c into @tn, @cn, @ln
end

close c
deallocate c

參考

• 將數據庫或表中的所有 NCHAR 列轉換為 NVARCHAR 列

一個簡單的例子：

CREATE TABLE dbo.x(a INT, b NCHAR(4));
GO
CREATE VIEW dbo.vx AS 
 SELECT a, b FROM dbo.x;
GO

ALTER TABLE dbo.x ALTER COLUMN a TINYINT;
ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4);
GO

SELECT a,b INTO #blat FROM dbo.vx;
GO

EXEC tempdb.dbo.sp_columns N'#blat';
GO

DROP VIEW dbo.vx;
DROP TABLE dbo.x, #blat;

部分輸出：

COLUMN_NAME    TYPE_NAME    PRECISION
-----------    ---------    ---------
a              tinyint      3
b              nvarchar     4

因此，實際上，是的，與視圖的任何互動都應該產生新的數據類型。

也就是說，我總是會呼叫sp_refreshview任何引用已更改表的視圖（實際上我經常使用WITH SCHEMABINDING這樣我就無法在不知道它影響的視圖和其他對象的情況下更改表 - 這可以使牛仔開發/部署很痛苦，但我想這就是重點）。

您可以建構腳本以像這樣動態刷新所有引用的視圖（這是針對單個表；您需要將其合併到現有腳本中以使其對所有受影響的表動態）：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC sp_refreshview ''' 
 + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.views AS v
ON d.referencing_id = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE d.referenced_id = OBJECT_ID('dbo.whatever')
GROUP BY s.name, v.name;

EXEC sp_executesql @sql;

更改基表後應始終刷新所有視圖的另一個原因，特別是如果您的視圖使用SELECT *：

CREATE TABLE dbo.x(a INT, b NCHAR(4));
GO
CREATE VIEW dbo.vx AS 
 SELECT * FROM dbo.x;
GO

SELECT * INTO #b1 FROM dbo.vx;
GO

ALTER TABLE dbo.x ALTER COLUMN a TINYINT;
ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4);
GO

SELECT * INTO #b2 FROM dbo.vx;
GO

ALTER TABLE dbo.x ADD d INT;
GO

SELECT * INTO #b3 FROM dbo.vx;
GO

EXEC sp_rename N'dbo.x.b', N'c', N'COLUMN';
EXEC sp_rename N'dbo.x.d', N'b', N'COLUMN';
GO

SELECT * INTO #b4 FROM dbo.vx;
GO

EXEC tempdb.dbo.sp_columns N'#b1';
EXEC tempdb.dbo.sp_columns N'#b2';
EXEC tempdb.dbo.sp_columns N'#b3';
EXEC tempdb.dbo.sp_columns N'#b4';
GO

DROP TABLE #b1, #b2, #b3, #b4;
GO

部分結果：

-- initial:

#b1____    a    int        10
#b1____    b    nchar      4

-- correct:

#b2____    a    tinyint    3
#b2____    b    nvarchar   4

-- missing new column d:

#b3____    a    tinyint    3
#b3____    b    nvarchar   4

-- missing column c, b still points at "old" b:

#b4____    a    tinyint    3
#b4____    b    nvarchar   4

但是如果我們刷新視圖：

EXEC sp_refreshview N'dbo.vx';
GO

SELECT * INTO #b5 FROM dbo.vx;

EXEC tempdb.dbo.sp_columns N'#b5';

DROP VIEW dbo.vx;
DROP TABLE dbo.x, #b5;

結果：

#b5____    a     tinyint     3
#b5____    c     nvarchar    4
#b5____    b     int         10

請注意，數據類型現在是正確的，但列的順序不是您期望的。

引用自：https://dba.stackexchange.com/questions/61915