I have tested the technique described in example 1 of my article on a table that has 1.8 million rows.
When getting half to all of the rows I found that the time it takes to return those rows is the same.
When getting few rows from the table is when a difference in time is noticed and the query not using my technique got the rows faster.
The reason for this is that using my technique SQL Server pulls all the rows before reducing the result set based on the WHERE clause and using a query with a WHERE clause that has no variables and no CASE functions only pulls the rows it needs.
I'll do some research on this and plan to publish an article or two that describe performance issues with using dynamic SQL and static SQL using the CASE function.
Any suggestions for what would make such an article useful are welcome.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer