|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Index for JoinHi,
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 Ed 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. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Ed" <E*@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 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 > > Ed Ed (E*@discussions.microsoft.com) writes:
> If there are two tables with 1 to many relationship. It's difficult to answer for sure given this little information. Where> 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... 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, esq***@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 http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics