Order of columns in a compound Index

Question

I have a table that filters data on three columns: Country, State, City.

The queries in my application get more selective, but always include the more general column(s). For example, the WHERE clause would look like

WHERE Country = 'Canada'
WHERE Country = 'Canada' AND State = 'Ontario'
WHERE Country = 'Canada' AND State = 'Ontario' AND City = 'Toronto'

But wouldn't have a WHERE clause on just the City column or just the State column.

I'm tasked with creating an index for this table. And I understand that the column order matters in creating an index, but was confused on what would be more performant.

Here's the two options I'm thinking of:

Option 1:

(Country, State, City)

Option 2:

(City, State, Country)
(State, Country)
(Country)

The first option covers all the queries in one index. But the second option (I think) would perform better. What I wanted to ask is if there is an actual performance benefit in using Option 2?

Thoughts?

Sorry, if I didn't make much sense. Let me know if clarification is required. And Thank you!

Answer 1

Definitely go for Option 1.

This allows the exactly correct rows to be seeked (I.e. no more and no less) for all three of your identified query patterns.

There is no advantage of having indexes ordered with most selective column first if you never query based on that column on its own. The selectivity of that single column makes no difference for queries seeking on multiple columns.

The "most selective first" trope is an area quite misunderstood in the SQL Server community. I go into more detail here.