Hi Tony,

Depends upon the most common types of queries.

But yes, 99% of the time, you want to get to the smallest number of rows as quickly as possible, so if nothing else is important, I’d go with the higher cardinality column.

Of course there are exceptions.

For example, it could be that by using a different order in the keys, you could avoid expensive sorts later, etc.

Where are the articles that say differently? Can’t help but think they’d be edge cases, or particular types of scans rather than lookups.

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410 mobile

SQL Down Under | Web: https://sqldownunder.com | About Greg: https://about.me/greg.low

From: Tony Wright via ozdotnet <ozdotnet@ozdotnet.com>
Sent: Monday, 10 October 2022 10:27 AM
To: ozDotNet <ozdotnet@ozdotnet.com>
Cc: Tony Wright <tonywr71@gmail.com>
Subject: SQL server composite key column order

Hi peeps,

When setting up composite key indexes in SQL server, I have always thought that it is best to put the column that has the most variation first in the index. I've recently seen articles that claim this is a myth. 

So does anyone know the the facts here? Is column order important?

Regards, Tony