Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««123

Holy Foreign Keys and Indexes Expand / Collapse
Posted Thursday, October 13, 2011 9:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 12, 2016 2:14 PM
Points: 1,419, Visits: 4,611
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
Post #1189989
Posted Friday, October 21, 2011 7:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 23, 2016 2:01 PM
Points: 68, Visits: 364
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 »

Add to briefcase «««123

Permissions Expand / Collapse