如何在 SELECT INTO 中控制基于文字的列的可空性 [英] How to control nullability in SELECT INTO for literal-based columns

问题描述

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

I am noticing that when I use this statement, the Action column is not nullable:

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

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

But when I use this statement, the Action column is nullable:

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

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

My reason for creating the table this way is because I don't want the temp table to inherit the collation of SerialNumber from the server default collation or elsewhere. I want it to match the collation of FSE_SerialNumber..SerialNumber.

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

My question is, can I rely on the cast function giving me a nullable column, or is this not clearly defined and might change. Why does the cast suddenly make the column nullable? Is there a better way (besides comments) to clarify that my intent is to get a nullable column there?

推荐答案

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

It looks like a definitive answer is here. Copying here:

元数据是根据源列和使用的表达式确定的在 SELECT 列表中.规则如下:

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

1. 任何使用诸如 SUBSTRING、LEFT、RIGHT 等内置函数的表达式(ISNULL 除外)都被认为是 NULLable引擎.所以如果你使用 CAST(somecol as char(8)) 那么表达式是可空

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

文字、常量、全局变量(如@@DBTS、@@ERROR 等)被认为是不可为空的，因为它们总是返回一些值

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

如果表达式是一列，则可空性源自源列元数据

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

因此，要使 SELECT 列表中的表达式或列不为空，请在该列或表达式周围使用 ISNULL.

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

因此，您可以安全地使用 CAST 表达式.

So, it looks like you are safe to use your CAST expression.

这篇关于如何在 SELECT INTO 中控制基于文字的列的可空性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！