How can I help SQL Server recognize my view column is NOT NULL-able?

Question

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes):

CREATE VIEW dbo.balances
WITH SCHEMABINDING
AS
SELECT
      user_id
    , currency_id

    , SUM(transaction_amount)   AS balance_amount
    , COUNT_BIG(*)              AS transaction_count
FROM dbo.transactions
GROUP BY
      user_id
    , currency_id
;
GO

CREATE UNIQUE CLUSTERED INDEX UQ_balances_user_id_currency_id
ON dbo.balances (
      user_id
    , currency_id
);
GO

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions. However, when I look at the view definition in Management Studio's Object Explorer, it marks both balance_amount and transaction_count as NULL-able columns in the view.

I've taken a look at several discussions, this one being the most relevant of them, that suggest some shuffling of functions may help SQL Server recognize that a view column is always NOT NULL. No such shuffling is possible in my case, though, since expressions on aggregate functions (e.g. an ISNULL() over the SUM()) are not allowed in indexed views.

1. Is there any way I can help SQL Server recognize that balance_amount and transaction_countare NOT NULL-able?

2. If not, should I have any concerns about these columns being mistakenly identified as NULL-able?

   The two concerns I could think of are:

   • Any application objects mapped to the balances view are getting an incorrect definition of a balance.
   • In very limited cases, certain optimizations are not available to the Query Optimizer since it does not have a guarantee from the view that these two columns are NOT NULL.

   Is either of these concerns a big deal? Are there any other concerns I should keep in mind?

Answer 1

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions

It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases:

create table foo(bar integer not null);
select sum(bar) from foo
-- returns 1 row with `null` field

And is also true in the generalized versions of group by like cube

This simpler test case illustrates the point that any aggregate is interpreted as being nullable:

CREATE VIEW dbo.balances
with schemabinding
AS
SELECT
      user_id
    , sum(1)   AS balance_amount
FROM dbo.transactions
GROUP BY
      user_id
;
GO

IMO this is a limitation (albeit a minor one) of SQL Server - some other RDBMSs allow the creation of certain constraints on views that are not enforced and exist only to give clues to the optimizer, though I think 'uniqueness' is more likely to help in generating a good query plan than 'nullability'

Answer 2

I don't think there's any way you can force SQL Server to recognize these columns as not nullable, even though they clearly are not. You can try to change the order of how you define ISNULL/COALESCE around the expression inside SUM(), for example, but it's not going to help.

I also don't believe there are any optimizations you're going to miss out on - those columns aren't currently indexed, so it's not like the optimizer can choose a different access method to determine, say, all balance_amount values > 10000. There may be a situation where if you create a non-clustered index on one of those columns you might get slightly better estimates than if the index isn't there, but this has nothing to do with nullability.

I wouldn't be too worried about this from a performance perspective. I went back and looked at a bunch of indexed views I've created over the years and these aggregation columns are all nullable. They perform just fine.

As far as object mapping goes, again, I wouldn't be too worried about it. Since the application can't update the indexed view, it doesn't matter if it thinks that balance_amount can be null. It's never going to receive a null, and it can't try to write a null, so <shrug>.