Bdtjtk

I have a stored procedure like so:

select 

    TransactionDate 

from 

    (select 

         cast(TransactionDate as Date) as TransactionDate

     from RetailTransaction) t

However this makes the TransactionDate column of the outer-select nullable, while RetailTransaction.TransactionDate column is not null.

RetailTransaction.TransactionDate definition / design:

Inner Select:

Outer Select:

Even after adding isnull or coalesce SQL Server / SSMS still shows that the outer selects TransactionDate column is still nullable.

select 

    TransactionDate 

from 

    (select 

         isnull(cast(TransactionDate as Date), getdate()) as TransactionDate

     from 

         RetailTransaction) t

How do I make the TransactionDate column non nullable?

Note that the database is on Azure with compatibility level 100 (SQL Server 2008).

EDIT:

Adding an isnull on the outer select still make the column nullable to an outer-nested-query:

After reading your comment I did some digging and testing, and even though I couldn't find official documentation about it, you are correct and the casting to date makes the value nullable, even if the datetime is not nullable. I've even tried a different method by using datetimefromparts to create a date data type but that also changes the nullablility of the result.

So the way I see it, you have two options:

The first option is to add a persisted computed column to the table that will hold the date value of the transaction date. It has to be persisted to be non nullable:

ALTER TABLE RetailTransaction

    ADD TransactionDateOnly AS CAST(TransactionDate As Date) PERSISTED NOT NULL;

and then your query looks like this:

SELECT TransactionDateOnly as TransactionDate

FROM RetailTransaction

The other option would be to declare a table variable with a non-nullable date column, select the cast results into it, and then select from the table variable:

DECLARE @Target AS TABLE

(

    TransactionDate Date NOT NULL

)

INSERT INTO @Target(TransactionDate)

SELECT CAST(TransactionDate as Date) 

FROM RetailTransaction) t

And then your select looks like this:

SELECT TransactionDate 

FROM  @Target

Both ways will get you a non-nullable date as a result, but I think the persisted computed column option should probably yield better performance on the select, because it doesn't require that extra insert...select.

Yes the reason it is showing Nullable for not nullable column is because you are making your outer select as computed based on isnull condition and others.

In this table Email is not nullable column and now if I give Isnull condition in inner query it will be like this.

Now if I don't give any condition it will be like this.

You can refer to this link as well for detailed explanation.

https://dba.stackexchange.com/questions/114260/why-is-a-not-null-computed-column-considered-nullable-in-a-view

The trick is to put the ISNULL on the outer query and add an alias so that you don't lose the name

select 

    isnull(TransactionDate, GETDATE()) as TransactionDate

from ( 

    select 

        Cast(TransactionDate as Date) as TransactionDate

    from RetailTransaction

) t

That way SQL will realise that the field cannot be null. This should nicely inform any ORM that this field should not be mapped to a nullable type.