Holy Foreign Keys and Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James DiMauro » Holy Foreign Keys and Indexes

22 posts, Page 3 of 3

«««1 2 3

Holy Foreign Keys and Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

alen teplitsky

Posted Thursday, October 13, 2011 9:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 1,408, Visits: 4,505

if i understand this correctly there are two unique indexes that were created on the same column or columns. one clustered unique index and on PK unclustered. any reason for that? why not just use one or the other.

if this is the case it doesn't sound like a bug to me

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #1189989

Dan Guzman-481633

Posted Friday, October 21, 2011 7:13 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:30 AM
Points: 39, Visits: 228

SQL Server allows a foreign key to reference any column(s) that is guaranteed to be unique. The Books Online states "FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table."

When the foreign key is created, SQL Server will bind the foreign key to the index. There is no documented behavior on which constraint or index will be chosen when multiple candidate unique indexes exist. The rules SQL Server uses are apparently different between SQL 2005 and SQL 2008+. I don't think it's correct to classify a change in behavior as a defect unless it's contrary to documentation.

With some experimentation, I learned that SQL 2005 chooses the clustered index on a table and the first non-clustered (lowest index_id) on a heap. However, SQL 2008 and SQL 2008 R2 choose the first non-clustered index on both tables and heaps; the clustered index is used only if no candidate non-clustered indexes exist.

Post #1194746

« Prev Topic | Next Topic »

22 posts, Page 3 of 3

«««1 2 3

Permissions