1

I have the following unique index. 

CREATE UNIQUE NONCLUSTERED INDEX [IX_Unique_Company_Owners] ON [dbo].[Company_Owners]
(
    [Company_Entity_ID_FK] ASC,
    [Person_Entity_ID_FK] ASC
)

Does the column order make a difference? I know it does for non-unique indexes. But wasn't sure if SQL Server somehow tweaks unique indexes behind the scenes.

Note both columns are not null.

Stick-With-SQL
  • 213
  • 1
  • 2
  • 6

2 Answers2

2

The column order has the same performance effect on UNIQUE indexes as it does on non-unique.

The only way in which SQL Server treats UNIQUE indexes differently is that it checks for uniqueness when rows are being inserted or updated. For purposes of SELECT queries, unique and non-unique indexes are treated the same.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
1

It absolutely makes a difference.

Same difference as ordering a phone book by, say, last_name then first_name vs first_name then last_name.

If you're always querying by both values, it would not make a real world difference, but if you often query by just person, then this index would not be terribly helpful since it's second in the order.

Brett Green
  • 3,535
  • 1
  • 22
  • 29