How to make a view column NOT NULL

Question

I'm trying to create a view where I want a column to be only true or false. However, it seems that no matter what I do, SQL Server (2008) believes my bit column can somehow be null.

I have a table called "Product" with the column "Status" which is INT, NULL. In a view, I want to return a row for each row in Product, with a BIT column set to true if the Product.Status column is equal to 3, otherwise the bit field should be false.

Example SQL

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

If I save this query as a view and look at the columns in Object Explorer, the column HasStatus is set to BIT, NULL. But it should never be NULL. Is there some magic SQL trick I can use to force this column to be NOT NULL.

Notice that, if I remove the CAST() around the CASE, the column is correctly set as NOT NULL, but then the column's type is set to INT, which is not what I want. I want it to be BIT. :-)

Answer 1

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.

Answer 2

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.