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
20
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
20
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),只要它是列的兼容类型。