March 8, 2016 at 4:44 pm
Great article, thank you.
March 8, 2016 at 5:20 pm
I'm glad you enjoyed it.
March 28, 2016 at 2:15 pm
With regard to foreign key index binding, Dan Guzman blogged in depth about this :
"SQL Server chooses the index binding based on rules that vary by version so you will get different binding depending on your version of SQLServer. SQL Server 2005 chooses the clustered index when possible and, if no suitable clustered index exists, the first (lowest index_id) unique non-clustered index on the referenced column(s) is used.
In later versions (SQL 2008, SQL 2008R2 and SQL 2012), the foreign key is bound to the unique non-clustered index on the referenced column(s) with the lowest index_id when possible. Only when no suitable unique non-clustered index exists is the unique clustered index chosen."
Hope this contributes to the discussion.
March 28, 2016 at 2:39 pm
xoraclez (3/28/2016)
With regard to foreign key index binding, Dan Guzman blogged in depth about this :"SQL Server chooses the index binding based on rules that vary by version so you will get different binding depending on your version of SQLServer. SQL Server 2005 chooses the clustered index when possible and, if no suitable clustered index exists, the first (lowest index_id) unique non-clustered index on the referenced column(s) is used.
In later versions (SQL 2008, SQL 2008R2 and SQL 2012), the foreign key is bound to the unique non-clustered index on the referenced column(s) with the lowest index_id when possible. Only when no suitable unique non-clustered index exists is the unique clustered index chosen."
Hope this contributes to the discussion.
Did Microsoft ever say why they switched to preferring non-clustered indexes for this binding? What performance implications does it have, if any?
Tom
October 29, 2018 at 11:03 am
This was removed by the editor as SPAM
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply