Database Programming: Yet More On Optional Parameters

Ever since I mentioned that Calvin Hsia clued me in to the size of this blog's audience, the comment traffic here has seen a notable spike. I love it..

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.

     -wp

Comments

• Anonymous
  January 01, 2003
  Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored...
• Anonymous
  January 01, 2003
  Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...