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:

Should I create an index for:

Where should I put indexes for this table?

Answer 1

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

Answer 2

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