SQL Server – Nullable Result in Deterministic CASE in View

computed-columnnullsql serversql-server-2016view

I have a table, with Id as the primary key.

create table Anything
(
    Id bigint not null primary key identity(1, 1)
)

When I look at this table in Object Explorer, I see this image of course:

As you can see, column Id is not null.

Then I create a dummy view on this dummy table:

create view IdIsTwoView
as

select
    Id,
    (
        case
        when Id = 2
        then cast(1 as bit)
        else cast(0 as bit)
        end
    ) as IdIsTwo
from Anything

But this time, in Object Explorer I see this result:

As you can see, in spite of this fact that my case clause is all-encompassing and covers 100% of all records, and has an answer for all records, it's nullable.

Why does SQL Server have this weird behavior? And how do I force it to be not null?

P.S we have an infrastructure to generate code dynamically, and this behavior causes us trouble and we have to manually go and change all bool? types in C# to bool.

Best Answer

To answer the why part of your question, the CASE expression result is nullable because the CAST function result is nullable. This can be observed with:

EXEC sp_describe_first_result_set N'SELECT CAST(1 AS bit) AS result;';

Result (abbreviated):

+-----------+----------------+--------+-------------+----------------+------------------+
| is_hidden | column_ordinal |  name  | is_nullable | system_type_id | system_type_name |
+-----------+----------------+--------+-------------+----------------+------------------+
|         0 |              1 | result |           1 |            104 | bit              |
+-----------+----------------+--------+-------------+----------------+------------------+

The result is not null when a constant is specified without the function:

EXEC sp_describe_first_result_set N'SELECT 1 AS result;';

+-----------+----------------+--------+-------------+----------------+------------------+
| is_hidden | column_ordinal |  name  | is_nullable | system_type_id | system_type_name |
+-----------+----------------+--------+-------------+----------------+------------------+
|         0 |              1 | result |           0 |             56 | int              |
+-----------+----------------+--------+-------------+----------------+------------------+

But the result data type is int instead of the desired bit as SQL Server has no bit constant without implicit or explict conversion, ISNULL is needed to coerce a not null result as @MichaelGreen answered.

Related Solutions

Mysql – How to define the datatype of a view in MySQL

You can hunt down the column definitions in the INFORMATION_SCHEMA. For example, for the table mydb.mytb and the column name needed is called mycol, do this:

SELECT column_type FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytb'
AND column_name='mycol';

For the query with formulas that you have in your question:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

Try creating your own empty temp table using your query with a FALSE-evaluated WHERE clause:

CREATE TABLE IF NOT EXISTS test.mycolumnmap
SELECT id, FLOOR(date_id / 100) month_id, SUM(value) value FROM some_table WHERE 1=2;

That table, though empty, has a definite table structure and some columns types defined. Now, just run the same query to get the column types from the temp table:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='mycolumnmap'
AND column_name IN ('id','month_id','value');

Another alternative is to just ask the INFORMATION_SCHEMA about the columns of the view directly:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='view_name';

Give it a Try !!!

Sql-server – In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns

Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN   
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable 
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable 
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + ' 
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' 
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1     
END

SELECT DISTINCT * FROM ##nullable 
WHERE hasNulls = 0  

DROP TABLE ##nullable

Best Answer

Related Solutions

Mysql – How to define the datatype of a view in MySQL

Sql-server – In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns

Related Question