October 11, 2011 at 6:46 am
I don't think I'd call this a bug. SQL Server can use the Primary Key or a Unique Constraint/index to as the reference column(s) for a foreign key. In this case (or these cases for those who have experienced it), SQL Server is probably using a unique clustered index to enforce a foreign key because it believes that it will improve performance. I'd have to assume that the lead column in the unique clustered index is the same column as the primary key. I'd be checking to see if both indexes are needed and perhaps make the unique clustered index the PK.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 11, 2011 at 12:36 pm
Hi,
I hope you guys can help me get something straightened out here. I ran this query on the Master DB:
select f.name
, i.name
, object_name(i.object_id) as tablename
, i.is_unique,i.is_primary_key
, i.type_desc
, f.key_index_id
from sys.foreign_keys f
join sys.indexes i
on i.object_id = f.referenced_object_id
and i.index_id = f.key_index_id
With these results.
name name tablename is_unique is_primary_keytype_desckey_index_id
FK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED1
FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED1
FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypesWorkOrderTypes 1 1 CLUSTERED1
FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypesWorkOrderTypes 1 1 CLUSTERED1
FK_ProjectWorkOrderEmployees_ProjectWorkOrdersPK_ProjectWorkOrdersProjectWorkOrders 1 1 CLUSTERED1
I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.
Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys.
Very sorry if this is too far off topic. If so I will repost as a new thread.
Thank you,
Richard
October 11, 2011 at 2:12 pm
rstelma (10/11/2011)
Hi,I hope you guys can help me get something straightened out here. I ran this query on the Master DB:
select f.name, i.name
, object_name(i.object_id) as tablename
, i.is_unique,i.is_primary_key
, i.type_desc
, f.key_index_id
from sys.foreign_keys f
join sys.indexes i
on i.object_id = f.referenced_object_id
and i.index_id = f.key_index_id
With these results.
name name tablename is_unique is_primary_keytype_desckey_index_id
FK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED1
FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED1
FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypesWorkOrderTypes 1 1 CLUSTERED1
FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypesWorkOrderTypes 1 1 CLUSTERED1
FK_ProjectWorkOrderEmployees_ProjectWorkOrdersPK_ProjectWorkOrdersProjectWorkOrders 1 1 CLUSTERED1
I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.
Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys.
Very sorry if this is too far off topic. If so I will repost as a new thread.
Thank you,
Richard
I wouldn't expect to see any results for user tables when running that query. Those DMV's are scoped to the database that you are in. If there are FK's showing in master that means the tables, indexes, and foreign keys were created in master instead of the user database or in addition to the user database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 11, 2011 at 4:33 pm
Hi Jack,
Damn database gremlins.
Now when I run the query on the Master I get no results and 156 on the DB where the tables exist and where I expect to see the Foreign Keys. I swear I ran that on the Master and kept switching back and forth.
Anyway... thank you. Very much appreciate your reply.
Richard
October 11, 2011 at 7:30 pm
Thank you for sharing this obscure issue. I hope it saves me time someday.
October 13, 2011 at 9:45 am
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
October 21, 2011 at 7:13 pm
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.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply