How to Help SQL Server Recognize Indexed View Column as NOT NULL

constraintnullsql-server-2008view

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?

Best Answer

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'

If the nullability of the column is important, perhaps for use with an ORM, consider wrapping the indexed view in another view that simply guarantees the non-nullability using ISNULL:

CREATE VIEW dbo.balancesORM
WITH SCHEMABINDING
AS
SELECT 
    B.[user_id],
    B.currency_id,
    balance_amount = ISNULL(B.balance_amount, 0),
    transaction_count = ISNULL(B.transaction_count, 0)
FROM dbo.balances AS B;

SSMS Object Explorer Details

Related Question