You’ve got tables. So many tables And columns. So many columns.
Users — demanding as they are — might wanna see, search, and order by all sorts of things in those many columns in those many tables. Writing a query to do that is hard at first, especially if you’re afraid of dynamic SQL.
Over the years, developers have come up with all sorts of “workarounds” for handling optional search parameters. The problem is that none of them work, whether it’s:
- column = @parameter or @parameter is null
- column = isnull(@parameter, column)
- column = coalesce(@parameter, column, ‘magic’)
Or any variation thereof. Doing this can (and will!) screw up query performance in all sorts of ways that sneak up on you.
- Bad cardinality estimates
- Scanning instead of Seeking in indexes
- Using the “wrong” indexes
In the video below, I’ll show you how to use dynamic SQL the right way to handle optional parameter search scenarios. This video is a small part of my paid training. If you like what you see, there’s a link for 75% off the entire package at the bottom of the post.
Optional Parameters Video
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Software Vendor Mistakes With SQL Server: Dealing With Bad Parameter Sniffing
- Software Vendor Mistakes With SQL Server: Writing And Optimizing Paging Queries
- Software Vendor Mistakes With SQL Server: IF Branching In Stored Procedures
- Software Vendor Mistakes With SQL Server: Using Left Joins To Find Rows That Don’t Exist