Wednesday, March 7, 2012

Does Column Order Affect Clustered Index Performance?

In a table definition, does the physical location of the columns that make up the clustered index affect the performance of a clustered index?

Table A
Name varchar(30) Not NULL
Home_Phone char(10) NULL
Other_Phone char(10) NULL
Company_ID char(9) NOT NULL <--PRIMARY KEY
Location_# int NOT NULL <--PRIMARY KEY
Sex char(1) NOT NULL
Age int NOT NULL

Table B
Company_ID char(9) NOT NULL <--PRIMARY KEY
Location_# int NOT NULL <--PRIMARY KEY
Name varchar(30) Not NULL
Home_Phone char(10) NULL
Other_Phone char(10) NULL
Sex char(1) NOT NULL
Age int NOT NULL

I always thought it did, but I can't find any documentation to back this up. Perhaps I'm mistaken.

Thanks, DaveI don't believe the order of the columns make a difference. You just want to know that the clustered indexes is stored in sorted order at the leaf level, on inserts, updates, the clustered index is taken into consideration.

HTH

No comments:

Post a Comment