CAST/CONVERT makes expressions nullable

I came upon this issue when I was building some views to support legacy integrations to an app that I was refactoring. The view is supposed to have exactly the same column definitions as a table in the old database that I am redesigning, so to make SSIS packages and other integrations run smoothly, I want the view’s columns to have the same datatypes, nullability, etc.

But there are some gotchas to watch out for with CAST and CONVERT.

The problem

Suppose the original table looks like this:

CREATE TABLE old.the_table (
    id            varchar(32) NOT NULL,
    [row]         int NOT NULL,
    dt            datetime NOT NULL,
    PRIMARY KEY CLUSTERED (id)
);

Now, with the new solution, we have a new table that, among other changes, has cleaned-up names and datatypes:

CREATE TABLE new.the_table (
    id               char(32) NOT NULL,
    [row]            smallint NOT NULL,
    date_loaded      date NOT NULL,
    date_processed   date NULL,
    PRIMARY KEY CLUSTERED (id)
);

Suppose we want to set up a view in the new solution that mirrors the names and definitions of the old table, so the legacy integration can use that view going forward:

CREATE OR ALTER VIEW new.the_table_like_before
AS

SELECT CAST(id AS varchar(32)) AS id,
       CAST([row] AS int) AS [row],
       CAST(date_loaded AS datetime) AS dt
FROM new.the_table;

Now, if you check out the resulting datatypes of the view, you’ll notice that all the columns are marked nullable, even though they’re all based on non-nullable columns, so the values in the view could never be null.

A possible solution

The origin of this problem is the fact that almost all computed expressions result in nullable output columns, including datatype conversions. One exception is the ISNULL function – if the second argument is non-nullable, the ISNULL expression will also be non-nullable. We can use this to accomplish our goal:

CREATE OR ALTER VIEW new.the_table_like_before
AS
SELECT ISNULL(CAST(id AS varchar(32)), '-') AS id,
       ISNULL(CAST([row] AS int), -1) AS [row],
       ISNULL(CAST(date_loaded AS datetime), {d '1900-01-01'}) AS dt
FROM new.the_table;

In fact, we don’t even need hard-coded values in the second argument of the ISNULL – this almost works the same:

CREATE OR ALTER VIEW new.the_table_like_before
 AS
 SELECT ISNULL(CAST(id AS varchar(32)), id) AS id,
        ISNULL(CAST([row] AS int), [row]) AS [row],
        ISNULL(CAST(date_loaded AS datetime), date_loaded) AS dt
 FROM new.the_table;

I found that the implicit char-to-varchar and smallint-to-int conversion works great with this method, but date-to-datetime doesn’t. If you know why, please let me know in the comments.

ISNULL or COALESCE?

On the surface, ISNULL and COALESCE appear to do pretty much the same job in SQL Server, but the fact that COALESCE can accept more than two parameters makes it the function of choice among many SQL Server developers. However, the two functions behave slightly differenty, as you can read more about in Aaron Bertrand’s excellent post on the subject.

One of the key differences is that ISNULL returns the datatype of the first argument, while COALESCE uses datatype precedence to determine the return type. So if you try COALESCE(something_varchar, something_int), the result will come out as an integer, whereas ISNULL(something_varchar, something_int) will convert the int value to the varchar datatype.

The other notable difference is that the COALESCE expression is nullable, but the ISNULL one isn’t (provided that at least the second argument isn’t, of course).

Why all this is important

I ran across this problem as part of building a backward-compatible view of a new database solution, but the issue is also relevant for other uses that require non-nullable expressions or columns, like a computed column that you want to put a primary key on.

This won’t work, for instance:

CREATE TABLE dbo.something (
    a       smallint NOT NULL,
    b       smallint NOT NULL,
    c AS (CAST(a AS int)+CAST(b AS int)) PERSISTED
);

ALTER TABLE dbo.something
    ADD PRIMARY KEY CLUSTERED (c);

Because the c column is computed, it’s nullable and you’ll get

Msg 8111, Level 16, State 1, Line 46
Cannot define PRIMARY KEY constraint on nullable column in table 'something'.
Msg 1750, Level 16, State 0, Line 46
Could not create constraint or index. See previous errors.

Oddly enough, if you include the primary key constraint directly in the CREATE TABLE statement, it works just fine:

CREATE TABLE dbo.something (
    a       smallint NOT NULL,
    b       smallint NOT NULL,
    c AS (CAST(a AS int)+CAST(b AS int)) PERSISTED,
    PRIMARY KEY CLUSTERED (c)
);