Finding Tagged Questions Faster: Answers & Discussion

Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag:

SELECT TOP 100 * FROM dbo.Posts WHERE Tags LIKE ‘%<sql-server>%’ ORDER BY Score DESC;

That’s currently using this index in its execution plan:

CREATE INDEX Score_Tags ON dbo.Posts(Score, Tags);

And answer 3 questions:

What kinds of tags will perform worse than others for this query? Could you change the query to perform better? Could you change the indexes to perform better, without changing the table structure? Q1: What tags will have problems?

The current execution plan is scanning the Score_Tags index