Will using an indexed view improve performance of SELECT COUNT queries?

Question

I have a table with that will grow to several million rows over some years. As part of my web application, I have to query the count on a subset of this table whenever a user accesses a particular page. Someone with an architecty hat has said that they have a performance concern with that. Assuming they are correct, will adding an indexed view address this issue?

Sql that I want to be fast:

SELECT COUNT(*) FROM [dbo].[Txxx] WHERE SomeName = 'ZZZZ'

OR

SELECT COUNT_BIG(*) FROM [dbo].[Txxx] WHERE SomeName = 'ZZZZ'

Table:

CREATE TABLE [dbo].[Txxx](
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [SomeName] [nvarchar](50) NOT NULL,
    [SomeGuid] [uniqueidentifier] NOT NULL
 CONSTRAINT [PK_Txxx] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)

View:

CREATE view dbo.Vxxx
WITH SCHEMABINDING
AS
SELECT     SomeName, COUNT_BIG(*) AS UsedCount
FROM         dbo.Txxx
GROUP BY SomeName

Index:

CREATE UNIQUE CLUSTERED INDEX [IV_COUNT] ON [dbo].[Vxxx] 
(
    [SomeName] ASC
)

score 4 · Accepted Answer · answered Sep 26 '12 at 22:12

Yes, but only Enterprise Edition will consider the indexed view during query compilation. To leverage the index on non-EE you need to select directly from the view and use the NOEXPAND hint:

NOEXPAND applies only to indexed views. An indexed view is a view with a unique clustered index created on it. If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view. This function is called indexed view matching. Automatic use of indexed view by query optimizer is supported only in specific editions of SQL Server.

Be warned that a indexed view like this will create write contention, because any update will lock and entire SomeName scope: only one transaction at a time will be able to insert, delete or update any row with SomeName = 'ZZZZ'.

Thanks for this answer. I want to accept it, but I'm worried by the fact that the count is not actually part of the index, so the documentation does not really cover this scenario. Am I missing something? — Steve Campbell, Sep 27 '12 at 14:30
I created some test data, but was unable to confirm the answer because it turns out all the SQL boxes here are SQL Standard. I did confirm that I have to use NOEXPAND and query the view directly in my scenario. Marking this answer as accepted. — Steve Campbell, Sep 28 '12 at 13:15

Aaron Bertrand · Answer 2 · 2012-09-26T22:26:21.757

Yes, that indexed view will definitely improve the performance of that particular query (assuming Enterprise Edition - Remus explains how to utilize it if you're not on Enterprise).

However, it isn't "free" - the index will need to be maintained for all DML operations to dbo.Txxx, will occupy space (though considerably less than the base table, in comparison), and will be subject to issues that also affect normal tables - such as fragmentation and (likely to a lesser extent in this case) page splits.

Will using an indexed view improve performance of SELECT COUNT queries?

2 Answers2