Full Article
Dynamic Search Conditions in
An SQL text by Erland Sommarskog, SQL Server MVP. Most recent update 2011-08-26.
There are two ways to attack this problem: dynamic SQL and static SQL. Up to SQL 2005, it was difficult to find solutions for static SQL that were simple to write and maintain and yet performed well, and the recommendation was to use dynamic SQL. In SQL 2008, things changed. Microsoft changed the hint OPTION(RECOMPILE) so it now works as you would expect. However, there was a serious bug in the original implementation, and you need at least CU5 of SQL 2008 SP1 or SQL 2008 SP2 to benefit from this feature.
Although, as we shall see, a solution with static SQL is in one sense more dynamic than dynamic SQL itself. There are still some performance implications, and a properly written solution with dynamic SQL can still be the best choice when you expect many concurrent searches. Dynamic SQL also remains the best choice when you need to support very complex search options.
This article exists in two versions. This version is for SQL 2008 SP1 CU5 and later. The other version is for SQL 2005 and earlier as well for SQL 2008 SP1 up to CU5. That version includes various tricks to deal with the performance problems of static SQL solutions that no longer are an issue with SQL 2008. Therefore, I have not included these tricks in this version.
In this text, I first look at a fairly common simple case of dynamic search conditions, that I've called "alternate key lookup" where the more general methods shoot over the target. I then introduce a typical problem of general dynamic search conditions that serves as a case study when I later discuss the solutions for static and dynamic SQL in detail....
Dynamic Search Conditions in T-SQL
Version for SQL 2008 (SP1 CU5 and later)
An SQL text by Erland Sommarskog, SQL Server MVP. Most recent update 2011-08-26.Introduction
CAUTION! This article does not apply to all versions of SQL 2008, only to SQL 2008 SP1 CU5 (10.0.2746) and SQL 2008 R2 CU1 (10.50.1702) and later. See the section A Tale of Service Packs and Cumulative Updates for more details.
A very common requirement in an information system is to have one or more functions where the users are able to search the data by selecting freely among many possible criterias. This is a tough challenge, because not only must you produce the desired output, but you must also keep the response time within acceptable limits, at least for common searches. And on top of all, the code must be maintainable, so that you easily can modify it to meet new needs and requirements.There are two ways to attack this problem: dynamic SQL and static SQL. Up to SQL 2005, it was difficult to find solutions for static SQL that were simple to write and maintain and yet performed well, and the recommendation was to use dynamic SQL. In SQL 2008, things changed. Microsoft changed the hint OPTION(RECOMPILE) so it now works as you would expect. However, there was a serious bug in the original implementation, and you need at least CU5 of SQL 2008 SP1 or SQL 2008 SP2 to benefit from this feature.
Although, as we shall see, a solution with static SQL is in one sense more dynamic than dynamic SQL itself. There are still some performance implications, and a properly written solution with dynamic SQL can still be the best choice when you expect many concurrent searches. Dynamic SQL also remains the best choice when you need to support very complex search options.
This article exists in two versions. This version is for SQL 2008 SP1 CU5 and later. The other version is for SQL 2005 and earlier as well for SQL 2008 SP1 up to CU5. That version includes various tricks to deal with the performance problems of static SQL solutions that no longer are an issue with SQL 2008. Therefore, I have not included these tricks in this version.
In this text, I first look at a fairly common simple case of dynamic search conditions, that I've called "alternate key lookup" where the more general methods shoot over the target. I then introduce a typical problem of general dynamic search conditions that serves as a case study when I later discuss the solutions for static and dynamic SQL in detail....
?l'ta?m