"Left Join Where Is Null" gets complicated to read/understand and is subject to cardinalities issues that will impact performance if the right set has many records for 1 record in...

Further to Paul's invitation I have prepared a more elaborated comparison between 3 methods we have talked about in this thread.

I hope that, and the end of my comparison, and...

Things become much more difficult when predicates are non-SARGable (necessitating index scans) or when ANDs are involved. The problem with an AND is that the net result of all...

By the way Jon, I think I understand why you have scans everywhere while trying the method in my post above: it's a bug in my code. Indeed, there...

It is difficult to demonstrate for me now because I am not at the office, the computer I use now does not have SQL... sorry if I take time to...

Jonathan AC Roberts (5/23/2010)

---

James Dingle,

In fact your query preforms nearly twice as badly as the original non-dynamic SQL that would have been in the initial stored procedure.

[...]

See SQL and execution...

I have experimented the approach above for a couple of years in a production system.

While it seems to simplify development on a very first time, personnally now I do not...

1) If you're in position of something that will be implemented but won't be effective, it seems the CFO makes the questions and the answers by already having chosen the...