How did a not null date become nullable in a view

Question

I have a datetime field in a table called dbo.Traffic

I am trying to aggregate traffic data by day. I am planing on creating a schemabound view and adding an index.

CREATE VIEW [dbo].[vwTraffic] WITH SCHEMABINDING AS

SELECT  CONVERT(date, CreateDate) as CreateDate, Circuit, Fuseaction,     
   COUNT(CreateDate) AS activity
FROM    dbo.Traffic WITH (NOLOCK)
GROUP BY CONVERT(date, CreateDate), Circuit, Fuseaction

In the base table, CreateDate is not nullable. However, as soon as I create a view and convert it to a date, it becomes nullable.

Answer 1

This is because of how computed columns behave in terms of nullability. Check it in here on MSDN:

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

You can use that as your CreateDate. You don't have to worry about the constant value as you should never have NULL:

ISNULL(CONVERT(date, CreateDate),0) as CreateDate