In case you’ve missed it in the comment stream from the original post in this thread, Adam Machanic left a comment commending an article on this topic by Erland Sommarskog. The article presents a detailed analysis of several methodologies for dealing with optional parameters.
While I’ve only had a chance to skim the article at this point, I did notice that none of the scenarios have been tested under SQL Server 2005. As soon as I get a few spare moments, I intend to verify these results in SQL Server 2000, and to also thoroughly analyze the query plans created by the different alternatives in SQL Server 2005.
Several changes have been made to index utilization in SQL Server 2005, including the ability to use a compound index even if the first argument in the index declaration is not present in the SARG. My impression is that this change is why my recommended syntax performs as well as it does in SQL Server 2005, but I need to verify that assumption.
Stay tuned, and thanks to those who have engaged in this discussion.