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

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

Answer 2

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 3

DROP TABLE [dbo].[myTable1]
GO

CREATE TABLE [dbo].[myTable1](
    [a] [int] NULL,
    [b] [varchar](50),
) ON [PRIMARY]
GO

INSERT INTO [dbo].[myTable1]([a],[b]) VALUES(1,'A');
INSERT INTO [dbo].[myTable1]([a],[b]) VALUES(2,'B');
INSERT INTO [dbo].[myTable1]([a],[b]) VALUES(3,'C');
INSERT INTO [dbo].[myTable1]([a],[b]) VALUES(4,'D');
INSERT INTO [dbo].[myTable1]([a],[b]) VALUES(5,NULL);


SELECT *, 
    (CASE b
        WHEN NULL THEN CAST(0 AS BIT)
        WHEN 'A' THEN CAST(1 AS BIT)
        WHEN 'B' THEN CAST(0 AS BIT)
        ELSE CAST(0 AS BIT)
    END) AS Status
FROM dbo.myTable1