This is because the filtering upon the RANK and ROW_NUMBER is a table scan operation). Walter, that's only true if one uses temp tables or table variables. If one uses a CTE to perform subsequent filtering based on any ranking functions (which is what I try to do as a rule), a table scan does not occur.
Additionally, both you and John Ryan assumed a table scan would occur in situations where you have multiple COALESCE statements in the WHERE clause. (Or at least one or both of your raised the question).
This is also not true. I tested it about 2 years ago when this went into production at a client site - it runs against a database with something like 1.1 million records, and customer service reps hitting it day and night.
However, I took another look at the execution plan tonight, and did some reading - apparently SQL Server, even 2005, doesn't handle COALESCE (or even ISNULL) as efficiently as one using it would hope. (Apparently DB2 handles it better, or at least so I read.)
In SQL Server 2005, the most efficient way to handle parameters (where only some are filled in) in stored procedures is to do the following:
WHERE (City LIKE @City OR @City is null) AND (Address LIKE @Address OR @Address is null)
So for the following 2 approaches:
1) A program that generates the string.... "select * from Customers where address like '%Eldorado%' and state = 'NY'"
OR
2) The following code:
SET @LastName = null
SET @FirstName = null
SET @Address = '%Eldorado%'
SET @City = null
SET @State = 'NY'
SET @Zip = null
SELECT * FROM Customers
WHERE (LastName LIKE @LastName OR @LastName is null) AND
(FirstName like @FirstName OR @FirstName is null) AND
(Address like @Address OR @Address is null) AND
(City like @City OR @City is null) AND
(State like @State OR @State is null) AND
(Zip like @Zip or @zip is null) The code that generates the statement dynamically has a slightly lower operator cost, while the straight stored procedure has a slightly lower CPU cost.
Now, the one thing I'm NOT taking into account is any execution time to generate the SQL statement and have the database execute it dynamically. I'm sure the time is very very small, perhaps as small as the differences in time for the I/O costs.
I'm going to post some specifics on my blog, but here are my conclusions:
1) You could have either solution running in production....and then swap it out for the other solution....and I have a hard time believing that anyone (not just users, but I.T. managers) would notice any measurable performance differences.
2) There's been this discussion for some time now, about whether stored procs are "flexible". I think I've made a strong case that straight stored procs can be as flexible as dynamic SQL.
Additionally, it's not secret that I'm a big proponent of the new language features in 2005 (as well as huge new ones in 2008, like the TABLE type, MERGE capabilities, and GROUP BY extensions). Only a small handful of new functions really make things go faster - the language enhancements are intended more to provide easier ways to get things done. As an instructor, I'd much rather teach a student how to query a hierarchical database using a CTE, then show him/her how to do it with previous techniques.
3) With performance being close, stored procedures are more about building a consistent API into your database, one that is client agnostic. Maybe in shops that are small in terms of developer staff, and/or only have one primary development tool, this may not seem like a strong argument. But scaling out dynamic SQL in more complex environments is going to be problematic - which is likely why the use of stored procs generally goes up in larger companies.
So I think the speed of one or the other isn't nearly as big a concern, when compared with the management of database code. Or....sometimes it comes down to choice - whether it's a choice that a developer is allowed to me, or being able to use either approach if the choice has been made for him/her.
