How to make a view column NOT NULL

FYI, for people running into this message, adding the ISNULL() around the outside of the cast/convert can mess up the optimizer on your view.

We had 2 tables using the same value as an index key but with types of different numerical precision (bad, I know) and our view was joining on them to produce the final result. But our middleware code was looking for a specific data type, and the view had a CONVERT() around the column returned

I noticed, as the OP did, that the column descriptors of the view result defined it as nullable and I was thinking It's a primary/foreign key on 2 tables; why would we want the result defined as nullable?

I found this post, threw ISNULL() around the column and voila - not nullable anymore.

Problem was the performance of the view went straight down the toilet when a query filtered on that column.

For some reason, an explicit CONVERT() on the view's result column didn't screw up the optimizer (it was going to have to do that anyway because of the different precisions) but adding a redundant ISNULL() wrapper did, in a big way.

You can achieve what you want by re-arranging your query a bit. The trick is that the ISNULL has to be on the outside before SQL Server will understand that the resulting value can never be NULL.

SELECT ISNULL(CAST(
    CASE Status
        WHEN 3 THEN 1  
        ELSE 0  
    END AS bit), 0) AS HasStatus  
FROM dbo.Product  

One reason I actually find this useful is when using an ORM and you do not want the resulting value mapped to a nullable type. It can make things easier all around if your application sees the value as never possibly being null. Then you don't have to write code to handle null exceptions, etc.