Personally I do not know, but I know how to rebuild statistics...
Mike Chaliy
2009-07-04 18:01:37
A:
+2
A:
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
2009-07-04 18:43:55
Execution plan stayed the same whether the table has any data or not in my case...
Sung Meister
2009-07-04 20:24:59
Remus, if the FK constraint is trusted, the optimizer will eliminate a lookup to the parent table, because there always is a parent row.
AlexKuznetsov
2009-07-04 20:58:16
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
2009-07-04 21:11:01
@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.
Sung Meister
2009-07-05 16:24:04
+6
A:
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?
AlexKuznetsov
2009-07-04 19:43:10
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...
Sung Meister
2009-07-04 20:24:29
+1 for "This answer is helpful". i did not know of any such thing as untrusted constraints.
Ian Boyd
2009-07-04 20:30:38