tag:blogger.com,1999:blog-5106689659668378141.post8797222351723858513..comments2024-01-03T13:12:30.352-05:00Comments on Wise man or Wise guy? You decide: Index on a Foreign Key?Anonymoushttp://www.blogger.com/profile/10597545408348167623noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-5106689659668378141.post-39125100452960584242012-08-16T03:58:48.114-04:002012-08-16T03:58:48.114-04:00Thank you JackThank you JackAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5106689659668378141.post-70796329688010599202012-08-15T13:34:05.013-04:002012-08-15T13:34:05.013-04:00While SalesOrderDetailID could be a primary key by...While SalesOrderDetailID could be a primary key by it self, I think the AdventureWorks design team chose to make it a copmosite key on SalesOrderID and SalesOrderDetailID because it is also the clustered key and they anticipate the insertion to be in ascending order by SalesOrderID and it doesn't make it necessary to have second index on SalesOrderID.Anonymoushttps://www.blogger.com/profile/10597545408348167623noreply@blogger.comtag:blogger.com,1999:blog-5106689659668378141.post-73631557110844376322012-08-15T12:29:13.573-04:002012-08-15T12:29:13.573-04:00Hello i would like to ask about the actual purpose...Hello i would like to ask about the actual purpose of the composite key in table SalesOrderDetail?<br /><br />I think that the field SalesOrderDetailId is actual primary key by itselfAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5106689659668378141.post-92217655539145138522009-10-09T21:36:23.130-04:002009-10-09T21:36:23.130-04:00Thanks Piotr. There are definitely some misconcep...Thanks Piotr. There are definitely some misconceptions around this subject.Anonymoushttps://www.blogger.com/profile/10597545408348167623noreply@blogger.comtag:blogger.com,1999:blog-5106689659668378141.post-40624122697203574972009-10-09T20:04:29.796-04:002009-10-09T20:04:29.796-04:00Hey Jack, this is quite a good piece of info. Acut...Hey Jack, this is quite a good piece of info. Acutally, it is a common misconception that foreign key columns need not to be indexed. In fact, indexing them as you said can improve performance by simply giving the optimizer more choices. It may be good to have index on foreign key columns just to give a chance to use merge join or nested loops join instead of hash join. This saves memory and CPU ticks big time.Piotr Rodakhttp://sqlblogcasts.com/blogs/piotr_rodak/noreply@blogger.com