troubleshooting Question

When to use a compound index and how to determine order of fields

Avatar of xersoft
xersoft asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
10 Comments1 Solution2026 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros