I have a SQL Server table with over 3 billion rows. One of my query takes an extremely long time so I am considering optimizing it. The query looks like this:

SELECT [Enroll_Date]
      ,Count(*) AS [Record #]
      ,Count(Distinct UserID) AS [User #]
  FROM UserTable
  GROUP BY [Enroll_Date]

The [Enroll_Date] is a low selectivity column with less than 50 possible values, while the UserID column is a high selectivity column with more than 200 million distinct values. Based on my research I believe I should create a non-clustered composite index on these two columns, and in theory the high selectivity column should be the first column. But I am not sure in my case, would that work because I am using the low selectivity column in the group by clause.

This table has no clustered index.

  • Can you post the actual execution plan xml (use pastebin and link it here) ? What version of sql server you are using ?
    – Kin Shah
    Commented Feb 17, 2016 at 20:34
  • 3
    The index with the highly selective column first will be useless for the specific query. Commented Feb 17, 2016 at 20:50
  • It IS best practice to use the higher selectivity column as the first key column in an index (normally). In this scenario, as you guessed, it doesn't help you at all. You might need two indexes! What happens when you use enroll_date first and user_id second?
    – paulbarbin
    Commented Feb 17, 2016 at 20:50

3 Answers 3


As an alternative to @AaronBertrand's solution (if you can't or don't want to create an indexed view), I would recommend you to create an index on (Enroll_Date, UserID). If this type of question is very common on your table, this should probably even be your clustered index.

I would not generally recommend high-selectivity indexes as a general "best practice", but rather look at what index will give your query the best performance.

An index on (Enroll_Date, UserID) will give your query a highly optimized, non-blocking query plan with Stream Aggregates.

Stream aggregate query plan

"Non-blocking" in this context means that the query doesn't need to buffer any significant amounts of data (like, for instance, a sort or hash aggregate would), which means it (a) starts returning rows immediately, and (b) consumes practically no working memory.

  • Funny, 4 seconds apart and the same answer.
    – usr
    Commented Feb 17, 2016 at 22:57

Aarons answer is a great solution. I'll answer the question assuming you don't want to take that approach.

The query that you posted will usually be executed by first grouping on (Enroll_Date, UserID), then again on (Enroll_Date). This optimization is new to SQL Server 2012. It takes effect in case of a single COUNT DISTINCT.

An index on those two columns in the specific order (Enroll_Date, UserID) will suffice to get an efficient plan that funnels an index scan into two consecutive Stream Aggregates. The opposite order would not enable that plan.

Therefore, use the order (Enroll_Date, UserID). You have no choice here.

  • 5 seconds apart and the same solution. Well played, sir. :) Commented Feb 17, 2016 at 22:57
  • @DanielHutmacher OMG, will we manage to almost match our posts for a 3rd time?! +1 to you! How could I not upvote an identical answer?
    – usr
    Commented Feb 17, 2016 at 22:58
  • Glitch in the Matrix. :) Commented Feb 17, 2016 at 23:02
  • Thank you very much. I am creating the index and will post the improvement after it's done. The server version is Microsoft SQL Server 2008 R2 on AWS, but I guess it's still the only choince regardless.
    – Thinkinger
    Commented Feb 18, 2016 at 13:18
  • @Thinkinger in case you're not accepting Aarons approach you've got a tough choice :)
    – usr
    Commented Feb 18, 2016 at 15:09

Sounds like an ideal scenario for an indexed view, which allows you to pay for calculations and aggregates at write time instead of query time.

CREATE VIEW dbo.MyIndexedView
  SELECT Enroll_Date, UserID, RawCount = COUNT_BIG(*)
  FROM dbo.UserTable
  GROUP BY Enroll_Date, UserID;

CREATE UNIQUE CLUSTERED INDEX CIX_miv ON dbo.MyIndexedView(Enroll_Date, UserID);

That will take some time to create, and of course will require maintenance throughout all DML operations, just like an index on the base table.

Now the query against this view would be quite similar - each row in the view now represents a distinct user/date combo, so that figure can be calculated by a single COUNT(*), while the total number of rows in the base table is already partially aggregated for you, now you just need to add them up using SUM per date:

SELECT Enroll_Date, 
  [Record #] = SUM(RawCount),
  [User #] = COUNT(*)
FROM dbo.MyIndexedView WITH (NOEXPAND)
GROUP BY Enroll_Date; 

Added NOEXPAND hint, after remembering this and this.

I can tell you without a doubt that this query will be faster than your current query (but not by how much), except in the rare case where you have exactly one user for each date (in which case the same amount of data will have to be read) and the columns we know about are the only columns in the base table's index. Whether that performance boost at read time is worth the extra work that will affect the write portion of your workload is something we can't tell you - you'll have to test it to measure the trade-off (no index is free).

And if you frequently use the same common WHERE clauses against Enroll_Date for specific, well-defined ranges (say, the current quarter or year to date), you could add matching filtered indexes that reduce that I/O even further (but there's always a trade-off).

You might also consider putting a clustered index on the base table. This doesn't seem to be one of those very rare use cases that benefit from a heap.

  • I just confirmed with our IT and it seems I can't create this kind of view. But still appriciate your advice, and it will help others who can use it.
    – Thinkinger
    Commented Feb 18, 2016 at 13:24
  • 1
    Does your IT think there is a significant difference between an indexed view and additional or different indexes on the base table? Not being combative, just curious, because a lot of people have misconceptions about indexed views. I like to think of them as an additional, skinnier clustered index on the table, but with fewer rows. Commented Feb 18, 2016 at 13:41
  • @Thinkinger also, indexed views are not EE-only. Indexed view matching is EE-only. You can directly target them using NOEXPAND.
    – usr
    Commented Feb 21, 2016 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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