Dynamic Searches In SQL

Kevin Feasel

2017-02-28

T-SQL

Kenneth Fisher looks at a few methods for dynamic searches in T-SQL:

Multiple Queries

Pro: We get a separate query plan for each combination of parameters so performance is great. (Well, as good as can be anyway.)
Con: Maintance stinks. We need 16 different queries when we have 4 parameters and the numbers increase dramatically as we add additional parameters. So any change we make to the base query will have to be changed 16 times, and/or adding a new parameter means careful logic and adding a bunch of new queries.

I’d consider this a gateway for Erland Sommarskog’s article on the topic.

Related Posts

COUNT And NULL

Bert Wagner explains some of the trickiness of COUNT and NULL values in SQL Server: One thing I see fairly often (and am occasionally guilty of myself) is using COUNT(DISTINCT) and DISTINCT interchangeably to get an idea of the number of unique values in a column. While they will sometimes give you the same results, […]

Read More

Fun With QUOTENAME

Louis Davidson shares some tips on using the QUOTENAME function: Or if you are Rob Volk (@sql_r on Twitter), and you want to create an annoying database on your best frenemy’s SQL Server that includes brackets in the name, like: This [database] Is Awesome You will need to do: CREATE DATABASE [This [database]] Is Awesome]; […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728