why is the nullability different for same column in same query in 2 identical databses

Question

I have 2 identical databases. 1 test db by me, and 1 production db by the customer

I have a (simplified) query that looks like this

ALTER VIEW nulltest as 
SELECT isnull(NextDate,getdate()) as TillDate  FROM (
SELECT   LEAD(FromDate) OVER (partition by personid ORDER BY fromdate) AS NextDate
FROM   Taxes)t

In reality the TillDate column will never be null, since it has an ISNULL function. But when i check the nullability of the query, i get 2 distinct answers. I check with this query

SELECT sys.columns.is_nullable FROM sys.columns  INNER JOIN sys.objects ON sys.columns.object_id = sys.objects.object_id and sys.objects.name='nulltest'

On my db, I get a 1, and on the prod server I get a 0.

What gives?

UPDATE

FromDate in both databases is datetime2, not null

@@Version

DB 1: Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Essentials 6.3 (Build 9600: )

DB 2: Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

What's the nullability of Taxes.FromDate in both databases? — Jeroen Mostert
Also, what does SELECT @@VERSION give on both systems, and what's the DB compat level? This could be a difference in optimizers. — Jeroen Mostert

MtwStark MtwStark · Accepted Answer · 2018-04-11T09:56:04

I think that the problem is related to updatable views.

is_nullable = 0 means that you can't set that column to NULL and in this case is_nullabe is related to TillDate column of nulltest view, and not its source FromDate column in Taxes (table/view/function I don't know what).

if you try to do something like:

UPDATE nulltest SET TillDate = NULL WHERE TillDate = GETDATE()

If TillDate is_nullable = 0 it should give you an error.

Now, the reason why in two different servers it gives you different nullability can be related to sql-server version and support for updatable views.

I had a similiar (unresolved) problem with is_identity_column with table valued functions. (see here)

Maybe in your db, where you get is_nullable = 1, this kind of views, using window functions (LEAD) will result in something similar to my table valued function and its problems..

why is the nullability different for same column in same query in 2 identical databses

1 Answers