I have fact table defined in part like this
[id] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL, --default GetDate()
[UserID] [int] NOT NULL,
I query this table a lot as you can imagine. In the vast majority of cases I query with a date range and a user id. In most cases I then filter based on other fields in the table but a constant is filtering on date range and the user id.
My question is what would be the best index to place on this table and why?
I could put a single index on both the userID and Date separately.
I could put a compound index on UserID, Date or on Date, UserID
My thought is that Date, UserID would be best.
My reasoning is that since items will be added with the current date in most cases the index shouldn't need to reorder itself as much. If the compound index was on UserID, Date then every time a row was added it would need to insert into the proper users section of the index and then in the proper date range.
Am I thinking about this correctly? Please provide details regarding any advice offered.