我可以在创建SQL Server视图时指定列类型吗？

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?

这有正确的语法吗？

1 个解决方案

#1

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

SQL Server必须推断出类型 - 但是如果需要，你可以强迫它：

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:

至于null / non-null方面，SQL Server必须推断出这一点。如果您知道的列不是null但SQL Server不正确，则可以将其包装在带有非null第二个参数的ISNULL语句中：

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.

然后它会将列描述为非null。第二个值无关紧要（毕竟，这是关于我自己的断言，列永远不会为null），只要它是列的兼容类型。

#1