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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2004-08-12 : 12:58:28
|
| When creating a Composite Index does it matter which column is listed first? Will it have any effect on Joins?If my Unique Composite Clustered Index is Column1, Column2 in the Parent table and my SQL Statement is this..SELECT Parent.*,Child.* From Parent LEFT JOIN Child on Child.Column2 = Parent.Column2 AND Child.Column1 = Parent.Column1will the Composite Index still be used?TIA |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-12 : 13:16:55
|
| Yes. If the join was only on Column2 then it wouldn't be used - but might be useful if the join was only on Column1.If joins are always on both columns then the order should be decided by the number of common values - put the one with the highest distribution first.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-12 : 13:22:51
|
| What do you mean by "Highest Distribution"? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-12 : 13:54:29
|
| like 1234567890would be better first than 111112222==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-12 : 14:50:27
|
| I'm sorry, I'm still confused or stupid.This database consists of imported databases from multiple customers, all having the same schema. The column1 I am talking about will be Customer_Number which is in every table. The Column2 is the original primary key column from the original tables, which could be char or int. The Customer_Number field was added to separate the data by customer, so that's why I am using a Composite Index.So which should go first in the Composite Index, Customer_No, or the original primary key column? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 14:56:20
|
| Here's an example of what he's talking about:Let's say you have Column1 and Column2 like in your example. Now let's look at some data:Column1 Column24000000 75757554000000 61234234000000 12341233111111 2341234In the above example, Column2 has the highest distribution as there is no data repeating itself. Column1 has a low distribution in this example. So for the composite index, you would use Column2, Column1.You can view the distribution of your existing indexes with DBCC SHOWCONTIG.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-12 : 15:36:16
|
| Good to know because I was thinking the exact opposite. I may have data like this...Column1 Column20000001 00000010000001 00000020000001 00000030000001 00000040000002 00000010000002 00000020000002 00000030000002 00000040000003 00000010000003 00000020000003 00000030000003 0000004etc...So if I understand correctly, searching would be faster when indexing on column2 first, because it does not have as many repeating values. Then again if the data extends as noted where column1 reaches a value of 0000200 (200 Customers) and Column2 has values of 000001,000002,000003,000004 for each Customer, now which is the highest distribution? I guess a Group By Column1 and a Group By Column 2 would tell me that. Thanks! |
|
|
|
|
|
|
|
|
Does Composite Index Column Order matter?