Can I specify column types when creating an SQL Server view?

Question

Seeking to enforce more strict type safety and make it easier to detect mistakes, I would like to specify column types of my view explicitly.

But while this works:

CREATE VIEW [dbo].[myview] (
    [a],
    [b],
    [c]
)
AS
SELECT 'a1', 'b1', 'c1';

this fails:

CREATE VIEW [dbo].[myview] (
    [a] nvarchar(32) NOT NULL,
    [b] nvarchar(32) NOT NULL,
    [c] nvarchar(32) NOT NULL
)
AS
SELECT 'a1', 'b1', 'c1';

Is there a correct syntax for this?

Solution

SQL Server has to deduce the types - but you can force its hand if you need to:

CREATE VIEW [dbo].[myview] (
    [a],
    [b],
    [c]
)
AS
SELECT
   CONVERT(nvarchar(32),'a1'),
   CONVERT(nvarchar(32),'b1'),
   CONVERT(nvarchar(32),'c1');

As for the null/non-null side of things, again, SQL Server has to deduce this. If you have a column that you know will be not null but SQL Server is getting it incorrect, you can wrap it in an ISNULL statement with a non-null second argument:

SELECT ISNULL(ColumnSQLServerThinksCanBeNull,'abc')

And it will then describe the column as not null. The second value doesn't matter (after all, this is about my own assertion that the column will never be null), so long as it's of a compatible type to the column.

Comments

[Fandango68]

I've tried this problem using CAST() instead of CONVERT() and the view still kept the original "guessed data types". CONVERT() forces it before the view "forces it". Thanks!

[alejandrob]

Not your fault, but SQL views are meant to simplfy queries, not that you have intimate knowledge of each column datatype in order to ease the DBs life.

[Damien_The_Unbeliever]

@alejandrob - SQL views can fulfil many different purposes. If you only use them to simplify queries, so be it, but don't dismiss that others may use them differently.

[alejandrob]

Indeed, last week I found a use for this, and works beautifully. Thanks.