Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Yes there are concerns, for example your ORM will create nullable types, which in turn will need extra care in code when using them, which is useless (or even misleading) in your case. –  Marcel Oct 16 '12 at 5:39

2 Answers 2

up vote 5 down vote accepted

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'

share|improve this answer
1  
FYI Aggregates can give NULL on non-null columns without GROUP BY clauses stackoverflow.com/q/2552086/27535 –  gbn Sep 10 '11 at 7:38
    
as per select sum(bar) from foo above - or do you mean something else? –  Jack Douglas Sep 10 '11 at 13:09
    
exactly like that –  gbn Sep 10 '11 at 13:25

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>.

share|improve this answer
1  
Pedantic note, [coalesce allows for nullable columns while isnull won't]. –  billinkc Sep 10 '11 at 3:56
    
@Aaron, about object mapping: I consider it worth to look at, since a mapper will likely generate useless/misleading objects with nullable types that will never be really used as such. –  Marcel Oct 16 '12 at 5:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.