Are these the correct Indexes for an Indexed View for Leaderboards Grouped By Hour/Day/Month?

Question

Here's and example of a script which creates an Indexed View Grouped By the Hour. I also have 2 others for Day and Month. The table UserPoints stores a record whenever points are awarded to the User with an exact DateTime timestamp called CreationDate.

CREATE VIEW [dbo].[HourlyPoints] WITH SCHEMABINDING AS 

SELECT 
     [Count]        = COUNT_BIG(*) --Required by SQL Server
    ,[UserId]
    ,[PointTypeId]
    ,[Points]       = SUM(Points) 
    ,[Hour]         = DATEADD(hour, DATEDIFF(HOUR, 0, [CreationDate]), 0)
FROM [dbo].[UserPoints]
GROUP BY 
     ProfileId
    ,PointTypeId
    ,DATEADD(hour, DATEDIFF(HOUR, 0, [CreationDate]), 0)
GO

CREATE UNIQUE CLUSTERED INDEX [IX_HourlyPoints_UserId_PointTypeId_Hour] ON [HourlyPoints] ([UserId],[PointTypeId],[Hour])
GO

CREATE NONCLUSTERED INDEX [IX_HourlyPoints_PointTypeId_Points_Hour] ON [HourlyProfilePoints] ([PointTypeId],[Points],[Hour]) INCLUDE ([UserId])
GO

I'm trying to make sure I have the right indexes to query this Indexed View for a leaderboard which will rank Users based on the PointType & Sum of Points.

For example, my leaderboard query looks like this if I want a leaderboard based on the number points Users have earned with the last 3 hours. (or any other time window).

SELECT * FROM [HourlyPoints]
WHERE [PointTypeId] = 1 --Gold Points 
  AND [Hour] >= '2013-06-13 01:00:00.000' 
  AND [Hour] < '2013-06-13 04:00:00.000'
ORDER BY Points

What I'm worried about is that when I run that query I don't see the Execution Plan window showing that it is using the IX_HourlyPoints_PointTypeId_Points_Hour index. Shouldn't it?

Answer 1

I figured out that I needed to use WITH(NOEXPAND)!

SELECT * FROM [HourlyPoints] WITH (NOEXPAND)
WHERE [PointTypeId] = 1 --Gold Points 
  AND [Hour] >= '2013-06-13 01:00:00.000' 
  AND [Hour] < '2013-06-13 04:00:00.000'
ORDER BY Points