adding an index to sql server

Question

I have a query that gets run often. its a dynmaic sql query because the sort by changes.

SELECT userID, ROW_NUMBER(OVER created) as rownumber
from users
where
  divisionID = @divisionID and isenrolled=1

the OVER part of the query can be:

• userid
• created

Should I create an index for:

• divisionID + isenrolled
• divisionID + isenrolled + each_sort_by_option ?

Where should I put indexes for this table?

Answer 1

Start with divisionID + isenrolled + userID as it will always be used

Answer 2

I'd start with

CREATE INDEX IX_SOQuestion ON dbo.users (divisionID, isenrolled) INCLUDE (userID, created)

• The created ranking is unrelated to the WHERE clause, so may as well just INCLUDE it (so it's covered) rather that in the key columns. An internal sort would be needed anyway, so why make the key bigger?

• Other sort columns could be included too

• userid is only needed for output, so INCLUDE it

• perhaps take isenrolled into INCLUDE if it's bit. Only 2 states (OK, 3 with NULL), so kinda pointless to add to the key columns