Do SQL Server Views update their data types automatically based on what tables feed them?

Question

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside.

In my script, I currently have it limited to pull only the tables that have nchar types, assuming that the views that are fed by the tables will know to update their values. Is this assumption correct, or should I be including views in this list as well?

For reference, the script in its current form is below (mostly taken from this great SO answer):

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

References

• Convert all NCHAR columns to NVARCHAR columns in a database or table

Answer 1

A simple example:

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;

Partial output:

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

So, in practical terms, yes, any interaction with the view should yield the new data types.

That said, I would always call sp_refreshview for any views that reference a table that has changed (and in fact I often use WITH SCHEMABINDING so that I can't alter a table without knowing about the views and other objects it affects - this can make cowboy development/deployment painful, but I guess that's kind of the point).

You can build the script to refresh all referenced views dynamically like this (this is for a single table; you'll need to incorporate this into your existing script to make it dynamic for all affected tables):

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;

Another reason you should always refresh all views after changing the base table(s), especially if your views use 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

Partial results:

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

But then if we refresh the view:

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;

Results:

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

Note that the data types are correct now, but the columns are not in the order you would expect.

• SeanKilleen: Great answer in several ways: made it understandable, gave an executable example, and provided two other tips I hadn't heard of before. Thank you! I'll be putting all of those (including testing with test tables, etc.) to use in the future.