CODEWITHSUNDEEP
sqlsql-server-2008nullablesql-view
René
René

Reputation: 10090

How to make a view column NOT NULL

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

Upvotes: 95

Views: 70560

Answers (4)

mariusz
mariusz

Reputation: 21

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;

Upvotes: 1

user1664043
user1664043

Reputation: 705

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.

Upvotes: 7

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

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.

Upvotes: 161

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: -3

Related Questions