Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: Yet More On Optional Parameters

Database Programming: Yet More On Optional Parameters

  • Comments 2
  • Likes

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
  • Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored...

  • Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment