View changes nvarchars to varchars in SQL Server 2008

Question

I have a view in a SQL Server 2008 db that simply exposes about 20 fields of one table to be consumed via ODBC to a client. When I tried to replicate this view in another database, the client could not consume the data source.

Then I noticed some weirdness. The columns in the view are shown, in SQL Server Management Studio, to be varchar(100), while the columns in the table are defined as nvarchar(100). There are no CAST or CONVERT statements in the view, it is a simple SELECT statement.

Example:

Table -> Columns:

Desc1 (nvarchar(100), null)

View -> SELECT TOP 100 PERCENT Desc1 FROM... Columns:

Desc1 (varchar(100), null)

Any ideas why the columns are defined as varchar in the view instead of nvarchar?

Answer 1

That happens when somebody updates a table but forgets to update the view.

Update the view. Just open it in SSMS as UPDATE VIEW and run it.

Answer 2

Run sp_refreshview and see if that fixes it, also take a look at how to make sure that the view will have the underlying table changes by using sp_refreshview

Answer 3

One answer would be the view definition is out of date

Try sp_refreshview and consider WITH SCHEMABINDING