Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2006-07-28 : 00:38:47
|
| Hi all, A question for the DBA's amongst us:Does the order of columns in a primary key or index affect performance? E.g. I have a table that has a primary key consisting of the first 3 columns. Currently they are declared in the primary key in the order of 2,3,1. Will it make any difference changing them to 1,2,3??Cheers,Tim |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-28 : 01:39:00
|
| what do you mean by order?reference to the pk is by combination of all 3 fields and by name instead of ordinal positionHTH--------------------keeping it simple... |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-28 : 01:46:09
|
I mean the order in which they are declared in the ALTER TABLE ADD CONSTRAINT statement:ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [Table1_PK] PRIMARY KEY CLUSTERED ( [field1], [field2] ) ON [PRIMARY] GO versus:ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [Table1_PK] PRIMARY KEY CLUSTERED ( [field2], [field1] ) ON [PRIMARY] GO |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-28 : 02:15:22
|
"Does the order of columns in a primary key or index affect performance?"Its a UNIQUE index, the cardinality is assured, so probably not. Everything else being equal: I'd put the one with the highest number of DISTINCT values first.However, the PK for a Child table better have the Parent Key first  otherwise the Foreign Key stuff isn't going to work very fast!Kristen |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-28 : 02:24:21
|
| Cheers Kristen.You're up early aren't you??? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-07-28 : 03:02:57
|
The order of columns in the index is significant if you are not selecting/joining on all of them. If you look at the execution plan for this totally lame example you will see the difference. DECLARE @table1 table ( ID int IDENTITY(1, 1) NOT NULL, Column1 int, primary key clustered (ID, Column1))SELECT Column1 FROM @table1 WHERE ID = 1 AND Column1 = 1SELECT Column1 FROM @table1 WHERE Column1 = 1 --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-28 : 03:07:49
|
| "You're up early aren't you???"Yes, but that's pretty normal for me!Kristen |
|
|
|
|
|
|
|
|
Primary key / Index ordinal