Index for Join

Hi,
If there are two tables with 1 to many relationship.
In order to speed up the join, what would be the major different if I put
the Clustered or Non-Clustered index on ForeignKey (Many Side) and
MajorKey(One Side)? If I put Clustered on both, would it be faster or it
doesn't matter if I put Non clustered' The reason I ask is because I would
like to put the clustered index on a date field instead of the key field.
since the key field join to another table, I am not sure if it will slow dow
n
the query if i remove the clustered and replace it with non clustered...
Thanks
EdIt is likely that a clustered index on the many-side table will increase per
formance of the join,
but other factors are also relevant. Test and check the execution plan and y
ou will see.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:1A44B506-86E8-4E57-A2FD-D3AFB4099B2C@.microsoft.com...
> Hi,
> If there are two tables with 1 to many relationship.
> In order to speed up the join, what would be the major different if I put
> the Clustered or Non-Clustered index on ForeignKey (Many Side) and
> MajorKey(One Side)? If I put Clustered on both, would it be faster or i
t
> doesn't matter if I put Non clustered' The reason I ask is because I wou
ld
> like to put the clustered index on a date field instead of the key field.
> since the key field join to another table, I am not sure if it will slow d
own
> the query if i remove the clustered and replace it with non clustered...
> Thanks
> Ed|||Ed (Ed@.discussions.microsoft.com) writes:
> If there are two tables with 1 to many relationship.
> In order to speed up the join, what would be the major different if I
> put the Clustered or Non-Clustered index on ForeignKey (Many Side) and
> MajorKey(One Side)? If I put Clustered on both, would it be faster or
> it doesn't matter if I put Non clustered' The reason I ask is because
> I would like to put the clustered index on a date field instead of the
> key field. since the key field join to another table, I am not sure if
> it will slow down the query if i remove the clustered and replace it
> with non clustered...
It's difficult to answer for sure given this little information. Where
to place the clustered index can often be a delicate question. But, to
take a little more concreate example, consider an Orders and an OrderDetails
table. For the OrderDetails it is typically a good idea to cluster on
the primary key (OrderID, RowNo), as you often retrieve all rows for an
order. On the other hand, Orders is likely to have a better column to
cluster on than the OrderId, for instance OrderDate or CustomerId.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx