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
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
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.
Is there any way I can help SQL Server recognize that
If not, should I have any concerns about these columns being mistakenly identified as
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
Is either of these concerns a big deal? Are there any other concerns I should keep in mind?