sql server programming: Index for Join -- sql server programming

Index for Join

Ed
12/23/2005 6:56:02 PM

sql server programming: 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 down
the query if i remove the clustered and replace it with non clustered...

Thanks

Re: Index for Join

Tibor Karaszi
12/24/2005 11:56:36 AM

It is likely that a clustered index on the many-side table will increase performance of the join,
but other factors are also relevant. Test and check the execution plan and you will see.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

[quoted text, click to view]

Re: Index for Join

Erland Sommarskog
12/24/2005 2:33:29 PM

Ed (Ed@discussions.microsoft.com) writes:
[quoted text, click to view]

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.