sql - 如何在 SELECT INTO 中控制基于文字的列的可空性

Question

我注意到当我使用这个语句时，Action列不可为空:

SELECT TOP 0 SerialNumber, 0 [Action] INTO #MyTable FROM FSE_SerialNumber

但是当我使用这个语句时， Action列可以为空:

SELECT TOP 0 SerialNumber, CAST(0 as int) [Action] INTO #MyTable FROM FSE_SerialNumber

我以这种方式创建表的原因是因为我不希望临时表从服务器默认排序规则或其他地方继承 SerialNumber 的排序规则。我希望它与 FSE_SerialNumber..SerialNumber 的排序规则相匹配。

我的问题是，我可以依靠 cast 函数给我一个可为空的列，还是没有明确定义并且可能会改变。为什么强制转换突然使列可以为空？有没有更好的方法(除了评论)来澄清我的意图是在那里获得一个可为空的列？

Answer 1

看起来确定的答案是 here .复制到这里:

Metadata is determined based on the source column and expressions used in the SELECT list. Below are the rules:

1. Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the engine. So if you use CAST(somecol as char(8)) then the expression is NULLable

2. Literals, constants, global variables like @@DBTS, @@ERROR etc are considered non-NULLable since they return some value always

3. If expression is a column then nullability is derived from the source column metadata

   So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.

因此，看起来您可以安全地使用 CAST 表达式。