Running MS SQL server 2014 express. Suddenly one of the columns in a view became nullable, despite being a union of two non-nullable columns. Please help me understand what's going on here:
and the UNION:
What am I missing?
Edit with more information: when I resave IncomingTransactions view, its column now becomes null, but it shouldn't be! Here's the definition of the Quantity column: (CASE PIN.StatusId WHEN 6 THEN PIN.QuantityReceived WHEN 7 THEN 0 ELSE PIN.QuantityRevised END) AS Quantity. Each of the quantity fields are non-null, and the case statement is exhaustive. The rest of the query is a simple join on the StatusId field, which is a non-null FK, so I'm still lost here.
Edit 2: based on YB's suggestions below, I created a minimal test case that reproduces this behaviour:
Create Table ybTest1 (Q1 decimal (7,2) not null, X int not null);
Create View ybTestNSB As
Select (CASE X WHEN 0 THEN Q1 ELSE CAST(0 as decimal(7,2)) END) AS Q From ybTest1
CAST(0 as decimal(7,2))
Just about every column that is computed as a result of an expression is regarded as nullable in SQL Server. The workaround is to use ISNULL as you are doing. This is mentioned in the computed columns section here
The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present ... An expression that is nullable can be turned into a nonnullable one by specifying
ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.
but it applies wherever a column is derived as a result of a calculation, including in a view definition.
There is little or no logic to analyse whether nulls are actually possible (sometimes more difficult than it seems as various deprecated set options can induce null rather than overflow errors so even
1 + X could produce a null in your example) and it errs on the side of caution. I don't see any way your
case expression can output
null in reality but in my experience pretty much any computed column will be treated as nullable except ones wrapped in
So in your test case you could replace
Create View ybTestNSB As Select (CASE X WHEN 0 THEN Q1 ELSE CAST(0 as decimal(7,2)) END) AS Q From ybTest1
Create View ybTestNSB As Select ISNULL(CASE X WHEN 0 THEN Q1 END, 0) AS Q From ybTest1
To avoid having to put an annoying entirely redundant expression there.