CODEWITHSUNDEEP
sql-serversql-server-2014sql-server-2016nullable
Yisroel M. Olewski
Yisroel M. Olewski

Reputation: 1628

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

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)

Upvotes: 3

Views: 83

Answers (1)

MtwStark
MtwStark

Reputation: 4058

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..

Upvotes: 0

Related Questions