How to control nullability in SELECT INTO for lite

问题:

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

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

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.

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?

回答1:

It looks like a definitive answer is here. Copying 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.

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

回答2:

Quick general hint for SELECT ... INTO : use ISNULL( with a column and it will be created as a NOT NULL column in the table.

回答3:

Personally, for the best control, you should create your table first. For example:

Create Table #MyTable (
   SerialNumber int Not Null, -- or however this is correctly defined.
   Action int Null
)

Then, do a Insert Into ... Select From ...

Insert Into #MyTable(SerialNumber, Action)
Select SerialNumber, 0
From FSE_SerialNumber

Then, there will be no question of what the fields should be.

I know this isn't quite what you asked, but it might be something to consider.