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<https://sqldownunder.com/> |
About Greg: https://about.me/greg.low
From: Tony Wright via ozdotnet
Sent: Monday, 10 October 2022 10:27 AM
To: ozDotNet
Cc: Tony Wright
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
