5

I have a weird problem where after setting nocheck on a foreign constraint and re-enabling it,

I am getting a same out-dated execution plan that was used with nocheck on.

Why would SQL server generate an execution plan as if foreign constraint FKBtoA is disabled even after adding the check again with following statement?

alter table B check constraint FKBtoA

[UPDATE1]
So far dropping foreign constraint and readding it worked. 

alter table B drop constraint FKBtoA
alter table B add constraint FKBtoA foreign key (AID) references A(ID)

But for really big tables, this seems like an overkill - Is there a better way?

[ANSWER]

I had to add WITH CHECK in alter statement like following to get the old execution plan

alter table B WITH CHECK add constraint FKBtoA foreign key (AID) references A(ID)

Here is a full SQL statement

create table A ( ID int identity primary key )
create table B ( 
    ID int identity primary key,
    AID int not null constraint FKBtoA references A (ID)
)

select  *
from    B
where   exists (select 1 from A where A.ID = B.AID)

alter table B nocheck constraint FKBtoA
GO
select  *
from    B
where   exists (select 1 from A where A.ID = B.AID)

alter table B check constraint FKBtoA
GO
select  *
from    B
where   exists (select 1 from A where A.ID = B.AID)

Here is the screenshot of execution plans per each SELECT statement

Before disabling foreign key constraint
alt text

After disabling foreign key constraint
alt text

After re-enabling foreign key constraint
alt text

Community
  • 1
  • 1
dance2die
  • 35,807
  • 39
  • 131
  • 194

3 Answers3

7

Most likely your constraint is enabled but not trusted, so there can be orphan rows in your child table. Read this great post by Hugo Kornelis:Can you trust your constraints?

Community
  • 1
  • 1
A-K
  • 16,804
  • 8
  • 54
  • 74
  • Ah, ha! it was "with check" clause that I needed to add in "alter table B add constraint" so it had to be "alter table B WITH CHECK add constraint ..."! And "sys.check_constraints.is_not_trusted" was set to 1 when I altered constrainted with NOCHECK... – dance2die Jul 04 '09 at 20:24
  • +1 for "This answer is helpful". i did not know of any such thing as untrusted constraints. – Ian Boyd Jul 04 '09 at 20:30
  • Here's a good article on the topic - http://explainextended.com/2009/10/15/constraints-and-the-optimizer-in-sql-server-foreign-key/ – EBarr Apr 01 '12 at 20:34
1

There doesn't seem to be any data in those tables, judging from both the scripts you posted and from the width of the connectors in the plan. Analyzing query plans on empty tables is largely irrelevant: at one single page read, the optimizer will almost certainly choose a full scan.

I assume you're doing this as some sort of experiment, in real world you should join those tables not use inner EXIST.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Execution plan stayed the same whether the table has any data or not in my case... – dance2die Jul 04 '09 at 20:24
  • Remus, if the FK constraint is trusted, the optimizer will eliminate a lookup to the parent table, because there always is a parent row. – A-K Jul 04 '09 at 20:58
  • right, my answer doesn't really address the OP question, while Alex's answer hits the nail on the head. My general point remains, that plans on empty tables are often completely different from the actual plan used on a populated table. Or in other words, here is a good answer to a different question nobody asked ;) – Remus Rusanu Jul 04 '09 at 21:11
  • @Remus: It was an experiment for some existing much *bigger* stored procedures I had to update and the SQL code I posted was just a small sample of what was actually happening. Let me play around if having data in tables does change execution plan or not. Thank you for the a way for me to look a different way to approach my problem. Thanks. – dance2die Jul 05 '09 at 16:24
0

Personally I do not know, but I know how to rebuild statistics...

Mike Chaliy
  • 25,801
  • 18
  • 67
  • 105