Sample Header Ad - 728x90

Modify an existing non clustered index or create a new index in SQL Server 2012

0 votes
1 answer
1584 views
I have a table with 150 million records and 8 indexes. One of the indexes is on userId. A lot of the current queries I have filter on userId and therefore this index works perfectly. For one of the queries, I have to expand the where clause to include regionId and productId columns. I have two options: I can create a new index with (userId, regionId, productId) or add these new columns to the existing index. Which would be a better option? If I modify the existing index, would it affect the other queries that already use userId? **My Goal:**
I want my current query which expands the where clause to include RegionId and productId to run faster but I don't want my other queries that only have userId in the where clause to be affected. If I add a new index on (userid, regional, productid) I can accomplish that but I'm not sure if I'm duplicating the index since I already have an existing index on userId which I can expand to include regionid and productId. But I'm not sure about the consequences of modifying the existing index. Thanks for your time!
Asked by lifeisajourney (751 rep)
Feb 26, 2023, 09:40 PM
Last activity: Feb 27, 2023, 09:36 PM