Does column order matter when applying constraints and/or indexes?

Question

Does column order matter when applying constraints and/or indexes?

MIJ

SSCommitted

Points: 1915
More actions
July 9, 2009 at 11:37 am

#208106

If I want to apply a unique constraint to a single column, does the order of that column relative to others make any difference?
Given this table, which stores what are essentially workflow items,
CREATE TABLE [dbo].[Item](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[Category] [varchar](50) NOT NULL,
[Field1] [varchar](255) NULL,
[ObjectID] [varchar](30) NULL,
[Field2] [varchar](255) NULL,
...
[SubmitDate] [datetime] NULL,
[ApproveDate] [datetime] NULL,
[CloseDate] [datetime] NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I want to add a unique constriant to the ObjectID column; should that column come "before" other non-indexed or less unique columns, e.g. between ItemID and Category?
Or am I confusing this idea with guidelines for composite indexes, where the order of columns belonging to the index does make a difference?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A unique constraint is implemented as a unique index, so order of columns matters just as much, and for the same reasons, as with a composite index.

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

That's the order of columns in the index key. The order that columns are specified in the table's definition has absolutely no meaning and no effect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MIJ SSCommitted Points: 1915 More actions · Answer 2

That's the order of columns in the index key. The order that columns are specified in the table's definition has absolutely no meaning and no effect.

Thanks, that answers my question perfectly. I knew that column order makes a difference for indexes spanning multiple columns, but I wasn't making the correct distinction between the table definition and the index definition, so that really helped. Nice articles, as well - thanks for the links.

MIJ

WayneS SSC Guru Points: 95460 More actions · Answer 3

GilaMonster (7/9/2009)
A unique constraint is implemented as a unique index, so order of columns matters just as much, and for the same reasons, as with a composite index.
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
That's the order of columns in the index key. The order that columns are specified in the table's definition has absolutely no meaning and no effect.

Gail...

Great articles. I've been struggling with column order placement in indexes where the columns are used in join conditions and where clauses... the indexes that the "Missing Index Details" in the SSMS 2008 Execution Plans generate vary greatly from I would think is the proper column layout for the index. Have you got anything that talks about those conditions?

Say the join condition is on the PK, and there is a where clause on (an)other field(s). Even though the PK is (naturally) highly selective, I'm finding that indexes that start with the field(s) in the where clause to be better left-side columns. A discussion on this would be very beneficial.

Thanks,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 4

It makes no difference at all to a unique constraint as it will always check for equality on all columns in the constraint/index, therefore the number of page reads will be the same regardless of column order.

Where it might impact performance is if that index gets used other than to enforce the constraint, in which case the above articles will hold true.