First off all, I have not tested this on SQL5, so I can only deduct from what I know of the optimizer.

Walter, the point is that twice you thought table scans would be done - neither time was that correct. OK, now you're saying you're basing this on what you know of the optimizer (and from a prior version of SQL Server, correct?)

Now, both of our solutions wind up doing index scans in many instances, depending on the data. For example, a not-rare user lookup might be to find someone in NY who lives on "something Island" and is on a Blvd:

select FirstName, LastName, Address, City, State, Zip 
     from Customers where state= 'NY' and city like '%ISLAND%'  AND ADDRESS LIKE '%BLVD%'

In this case, the execution plan (I'm sure you won't be shocked by this) does an index scan on city, an index seek on state, a sort, a key lookup, and a filter.

(As I'm sure you know, there are other instances where an index scan would occur, that's just one).

The following is a "hunch", I won't state it with certainty - I think your approach maybe be a tad faster than mine when the core filter result is fairly small, whereas mine might be a tad faster as the core result sets increase. But it's just a guess.


I'd not do it like that. If the Columns do not contain null values, you can do the following:

SET @City = ISNULL(@city, '%')
SET @Address = ISNULL(@Address, '%')
SELECT * FROM Clients WHERE City LIKE @City AND Address LIKE @Address


I actually like that - it didn't appear to impact the execution plan, but I like that for readability (and yes, THERE I agree with readability).

It would depend on a large number of factors. With the trick I gave you above there would almost be no difference at all, because the database would execute the exact same execution plan (because of eliminating conditions).

There wasn't a difference to begin with. Again, I like the code change for readability, but there was no real performance difference to begin with.

You won't get any argument from me that testing is crucial - for all scenarios.


Yes and no, sure it is important to show new features, but the student should be teached the basics of SQL and tackeling these kind of problems in the old way really is a must for everyone who really wants to get most out of it.

In a teaching/training environment, you have to be VERY careful with that approach, often that's not at all practical.


You draw your conclusion on one (fairly simple) example we did here, which is not representative for what is build out there. I'd rather teach a student to understand how the optimizer works and let them draw the conclusion themselves rather that presenting such thing as static fact.

That's fine, but one could conclude from these discussion that you think looking at execution plans is "for the other guy to worry about".