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. Introduction. It is very common in information systems to have functions where the users are able to search the data by selecting. Try the sql server management studio (version 2008 or earlier) from Microsoft. Download it from here. Not sure about the license, but it seems to be free if you. I have a huge XML document (over 2GB) exported from an automotive company system which I wanted to be able to export data from it to a CSV file to import into a. 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. 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. SQL 2. 00. 8. 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 search_orders 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 customer's national registration number. That is, what is called SSN. Microsoft SQL Server est un système de gestion de base de données (SGBD) en langage SQL incorporant entre autres un SGBDR (SGBD relationnel ») développé et. 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. 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. RAISERROR('No 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 don't return tens of thousands of customers.)If you need to return data from other tables as well, and you don't 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. RAISERROR('No 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 = N'ZZZZZZZ')) 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 find_customer @custno = 1. EXEC find_customer @natregno = '1. EXEC find_customer @custname = 'ABC'. EXEC sp_recompile find_customer - - flush the plan for the procedure. EXEC find_customer @natregno = '1. EXEC find_customer @custno = 1. EXEC find_customer @custname = 'ABC'. EXEC sp_recompile find_customer. EXEC find_customer @custname = 'ABC'. EXEC find_customer @custno = 1. EXEC find_customer @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 left(@custname, 1) < > '%'. OPTIMIZE FOR. Using ORIf you don't 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. With them, the chances are good that the optimizer will pick a plan that seeks all three indexes using index concatenation. However, thanks to the IS NOT NULL conditions, SQL Server will add Filter operators with a startup expression so that at run- time, only one index is accessed. I return to startup expressions in the section Optional Tables later in this article.)This strategy usually works well as long as the search terms are all in the same table and all are indexed, but rarely (if ever) if the search terms are in different tables. In any case, you should never use this strategy blindly, but always verify that you get the plan – and the performance – you intended. The Case Study: Searching Orders. We will now turn to a more general case with many search terms. We will work with implementing a stored procedure that. Northgale database, which is an inflated version of Microsoft's classic Northwind database. See later in this chapter how to install it. This is the interface that we expose to. How to install SQL Server 2. Management Studio. I have a distinct feeling of déjà vu. However, I know I’m not dreaming because I’ve written about installing SQL Server Management Studio before, but last time it was for SQL Server 2. Why oh why is it so complicated to install this piece of software!? This time I installed Visual Studio 2. SQL Server 2. 00. Express. Of course I needed SQL Server Management Studio though, so I downloaded it from Microsoft and tried to install it. And of course, when I went through the installation wizard, the option to install Management Studio didn’t seem to be there. That’s when I start bashing my head against the keyboard and asking ‘why does this have to be so hard!’.. Anyway, after a little investigation and persistence, I figured it all out. I even managed to grab some screenshots so that I would NEVER forget how to do this ever again… and hopefully it will once again help others who may be having the same problems. Install Service Pack 1 for Microsoft SQL Server 2. Service Pack 1 or else you won’t even be shown the option to install Management Studio. Run the SQL Server Management Studio installer. Click on ‘Installation’ in the left menu. Click on ‘New SQL Server stand- alone installation or add features to an existing installation’. It will ask you to install ‘Setup Support files’ - yup, you have to install this in order to install other features, so go ahead and install it. Once it has finished installing the ‘Setup Support files’, the wizard should move you onto ‘Installation Type’.As counter- intuitive as it seems, choose the ‘Perform a new installation of SQL Server 2.Now the wizard asks you to specify the edition of SQL Server 2.You’ll notice that both options given are greyed out – that’s what you want to see.Click next….Feature Selection’ – brilliant.This is what you were aiming for.Here you should see a couple of different features that you can install, namely, ‘Management Tools – Basic’. Adobe After Effects Cs4 Cc Light Sweep Download . Command And Conquer Ultimate Collection Keygens '>Command And Conquer Ultimate Collection Keygens . Make sure it is checked then click next. Proceed through the next few dialogs, each of which ask you the standard installation questions like installation location, and sooner or later, you should see a nice screen telling you that it has successfully installed ‘Management Tools – Basic’.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |