Field order in a composite index order with high selectivity and low selectivity fields 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.
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.



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