Dynamic Search Conditions in T SQLAn SQL text by Erland Sommarskog, SQL Server. MVP. Most recent update 2. Copyright applies to this text. See here for font conventions used in this article. SQL Server articles, scripts, and discussion groups. Introduction. It is very common in information systems to have functions where the users are able to search the data by selecting. When you implement such a function with SQL Server there are two challenges to produce the correct result and have good performance. When it comes to the latter, there is a key theme there is no single execution plan that is good for all possible search criterias. Rather, you want the query plan to be different depending on user input. PI315-3.png' alt='Install Sql Server 2005 Sp2 Without Sp1 Download' title='Install Sql Server 2005 Sp2 Without Sp1 Download' />Toad for SQL Server Version 6. Click here for previous versions Release Notes. Tuesday, 7 April 2015. Microsoft Silverlight library, learning resources, downloads, support, and community. Evaluate and find out how to install and use Silverlight. There are two ways to achieve this. You can write a static SQL query and add the hint OPTION RECOMPILE which forces SQL Server to compile the query every time. Or you can use dynamic SQL to build a query string which includes only the search criterias the user specified. We will look at both these approaches in this article. They are both viable, and a good SQL programmer should have both in his toolbox since both have their strengths and weaknesses. This article assumes that you are on SQL 2. A key feature of this article is OPTION RECOMPILE, a query hint that was introduced already in SQL 2. SiteAssets/TEC495228_Embeded/495228f.gif' alt='Install Sql Server 2005 Sp2 Without Sp1 Download' title='Install Sql Server 2005 Sp2 Without Sp1 Download' />SQL 2. And to be precise, you should be on at least Service Pack 2 of SQL 2. Service Pack 1 for SQL 2. R2 to take benefit of this feature. For a full discussion of how this feature has changed forth and back, see the section The History of Forced Recompilation, which also discusses a bug with OPTION RECOMPILE fixed in the autumn of 2. If you are still on SQL 2. SQL 2. 00. 0, there is an older version of this article where I cover additional techniques that are not equally interesting on SQL 2. OPTION RECOMPILE. I begin the article looking at some methods which are good for very simple cases where you only need to handle a very small set of choices and where the more general methods shoot over the target. The next chapter introduces the task in focus for the rest of the article the requirement to implement the routine searchorders in the Northgale database which I introduce this chapter. The two main chapters of this article look at implementing this procedure with static and dynamic SQL. Alternate Key Lookup. Using IF statements. Problems with dynamic search conditions come in several flavours. In the general case, there is a search form where the user can select between many search conditions, and this is also the main focus of this article. But. sometimes you encounter problems with a small number of conditions that are more or less mutually exclusive. A typical example would be a form where a user can look up a customer by entering one of 1 The name of the customer. The customer number. The customers national registration number. That is, what is called SSN. There are indexes on all three columns. None of the solutions in the main body in the article are not really suitable here. Forcing a recompile every time with OPTION RECOMPILE can add too much load to the system, particularly if these lookups are frequent. And dynamic SQL is just too much hassle for a simple problem like this one. So let us look at more lightweight solutions that fit this problem. Traffic Flow V5 Games. A very simple minded way is to use IF IF custno IS NOT NULL. SELECT. FROM customers WHERE custno custno. ELSE IF natregno IS NOT NULL. SELECT. FROM customers WHERE natregno natregno. ELSE IF custname IS NOT NULL. SELECT TOP 2. 00. FROM customers. WHERE custname LIKE custname. ORDER BY custname. RAISERRORNo search condition given, 1. The TOP 2. 00 for the search on customer name limits the output in case the user would enter a very short search string, so that we dont return tens of thousands of customers. If you need to return data from other tables as well, and you dont want to repeat the join, you could enter all matching customer numbers into a table variable or a temp table, and then do your final join IF custno IS NOT NULL. INSERT cust custno VALUES custno. ELSE IF natregno IS NOT NULL. INSERT cust custno SELECT custno FROM customers WHERE natregno natregno. ELSE IF custname IS NOT NULL. INSERT cust custno. SELECT TOP 2. 00 custno. FROM customers. WHERE custname LIKE custname. ORDER BY custname. RAISERRORNo search condition given, 1. JOIN customers cst ON cst. JOIN. There is however a potential performance problem here. No matter which choice the user makes, we want the optimizer to use the index on the chosen search column. But the way SQL Server builds query plans, this may not always happen. When the procedure is invoked and there is no plan in the cache, SQL Server builds the plan for the entire stored procedure and sniffs the current input values for the parameters. Say that the first user to make the search enters a customer number. This means that the branches for national registration number and customer name are optimised for NULL and under unfortunate circumstances this could lead to a plan with a table scan, which is not what you want. For an in depth discussion on parameter sniffing, see my article Slow in the Application Fast in SSMS. To prevent this from happening, there are a couple of precautions you can take. One is to push the three SELECT statements down into three subprocedures, but admittedly this is a bit bulky. Another approach is to add explicit index hints, but you should always be restrictive with index hints. For instance, what if someone renames the index That would cause the query to fail. Rather, the best option is probably to use the OPTIMIZE FOR hint SELECT TOP 2. FROM customers. WHERE custname LIKE custname. ORDER BY custname. OPTION OPTIMIZE FOR custname NZZZZZZZ This hint causes SQL Server to build the query plan for the value you specify. Obviously you should pick a value which is selective enough. Whatever strategy you choose, you should test on production size data that you get the plans you expect. Due to the sniffing issue, your test should look something like this EXEC findcustomer custno 1. EXEC findcustomer natregno 1. EXEC findcustomer custname ABC. EXEC sprecompile findcustomer flush the plan for the procedure. EXEC findcustomer natregno 1. EXEC findcustomer custno 1. EXEC findcustomer custname ABC. EXEC sprecompile findcustomer. EXEC findcustomer custname ABC. EXEC findcustomer custno 1. Torrent Horse Racing Manager 2 here. EXEC findcustomer natregno 1. That is, you should test with all three parameters as the parameter sniffed when the plan is built. In this particular example, there is one more issue with the custname parameter that I have ignored so far the user could add a leading, in which case a scan would be a better choice. If you need to support searches with a leading, the best is to split this into two branches IF leftcustname, 1 lt. OPTIMIZE FOR. Using ORIf you dont like the multiple IF statements, you may be delighted to know that it is in fact perfectly possible do it all in one query as long as you can ignore leading in custname SELECT TOP 2. FROM customers. WHERE custno custno AND custno IS NOT NULL OR. AND natregno IS NOT NULL OR. LIKE custname AND custname IS NOT NULL. ORDER BY custname. The WHERE clause here essentially reads custno custno OR natregno natregno OR custname LIKE custname But the added conditions with IS NOT NULL serve a purpose.