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

Lillian

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.

Effeminacy

@Gunder: no worries, it's a bit arcane actually. This is also handy to use when creating a calculated bit column in a table and wanting the result to be not nullable. – Denote

I needed something similar, and I found that COALESCE() did not work, you actually have to use ISNULL() – Columbian

@Columbian That's odd, because both COALESCE and ISNULL can return NULL. Just a compiler quirk I guess. – Denote

This, times a billion, for getting EntityFramework to infer a key when one normally wouldn't be inferred. – Bonheur

See also: dba.stackexchange.com/questions/114260/… – Consummation

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.

Rubetta

Could you show the solution for how to ensure/indicate non-nullability with CONVERT() in an example, please? – Chordate

Hi O. R. - sorry I didn't see this for a while. Here's an example. If you have CONVERT(BIT,U.RETIRED),0) AS Retired in your view, turning say a byte or an int column into a bit/bool, then it becomes nullable. You can make that column in your view non-nullable by replacing it with ISNULL(CONVERT(BIT,U.RETIRED),0) AS Retired. If U.RETIRED was not null to start, it functionally doesn't change anything except the column in the view. WARNING: the ISNULL() can interfere with query optimization and choice of indicies. – Rubetta

Answer 3

small note to ISNULL() solution. This seems to work only if you provide a constant value as the second argument.

I was fighting with this while casting datetime column to date. The original column was not null, but after CAST it became null of course, so I used ISNULL() with function getdate() that never gives null.

It failed for some reason, so I tried to use original column which also is non-nullable with the same result. Finally I succeeded when I put constant date there.

 CREATE OR ALTER VIEW dbo.TestView AS
 SELECT 
    MyNotNullDatetimeColumn AS NonNullDatetime,
    ISNULL(CAST(MyNotNullDatetimeColumn AS DATE), GETDATE()) AS NullableDate,
    ISNULL(CAST(MyNotNullDatetimeColumn AS DATE), MyNotNullDatetimeColumn) AS StillNullableDate,
    ISNULL(CAST(MyNotNullDatetimeColumn AS DATE), '20010101') AS NonNullableDate
 FROM dbo.MyTable;

Revitalize

Answer 4

All you can do in a Select statement is control the data that the database engine sends to you as a client. The select statement has no effect on the structure of the underlying table. To modify the table structure you need to execute an Alter Table statement.

1. First make sure that there are currently no nulls in that bit field in the table
2. Then execute the following ddl statement: Alter Table dbo.Product Alter column status bit not null

If, otoh, all you are trying to do is control the output of the view, then what you are doing is sufficient. Your syntax will guarantee that the output of the HasStatus column in the views resultset will in fact never be null. It will always be either bit value = 1 or bit value = 0. Don't worry what the object explorer says...

Religiose

I don't want to change the table column. The column is defined as an integer column, that allows null. This fits our spec. But I need a view that returns a column with a bit field, that cannot be null. It's not sufficient that I know it can't be null, the column has to be NOT NULL, so that it will map correctly in our ORM. – Goodly