5

I have the following query in SQL Server 2005 which works fine:

DECLARE @venuename NVARCHAR(100)
DECLARE @town NVARCHAR(100)

SET @venuename =  NULL -- normally these are parameters in the stored proc.
SET @town = 'London'

SELECT COUNT(*) FROM dbo.Venue
WHERE 
    (@VenueName IS NULL OR CONTAINS((Venue.VenueName), @VenueName))
AND 
    (@Town IS NULL OR Town LIKE @Town + '%')

It uses short-circuiting when null values are passed for the parameters (there are many more in the real SP than shown in my example).

However after upgrading to SQL 2012, running this query with NULL passed for @VenueName fails with the error "Null or empty full-text predicate" as SQL Server seems to be running (or evaluating) the CONTAINS statement for @VenueName even when @VenueName is set to NULL.

Is there a way to use short-circuiting in 2012 or is this no longer possible? I'd hate to have to rewrite all of my SPs as we've used this technique in dozens of stored procedures across multiple projects over the years.

NickG
  • 9,315
  • 16
  • 75
  • 115

3 Answers3

3

I do not know much about sql 2012 but can you please try following 

DECLARE @venuename NVARCHAR(100)  
DECLARE @town NVARCHAR(100)

SET @venuename =  '""' -- -- **Yes '""' instead of null**.
SET @town = 'London'

SELECT COUNT(*) FROM dbo.Venue
WHERE 
    (@VenueName ='""' OR CONTAINS((Venue.VenueName), @VenueName))
AND 
    (@Town IS NULL OR Town LIKE @Town + '%')
Dhaval
  • 2,801
  • 20
  • 39
  • 1
    That works, but the query plan shows it's actually performing a full text search. I was trying to avoid that if possible (hence the short-circuiting). – NickG Dec 13 '13 at 12:29
1

Check out this thread: OR Operator Short-circuit in SQL Server Within SQL server, there is no guarantee that an OR clause breaks early. It's always been that way, so I guess you've just been lucky that it worked with SQL Server 2005.

To workaround your problem, consider using the ISNULL function every time you supply a parameter value that might be NULL, to the CONTAINS function.

Community
  • 1
  • 1
Dan
  • 10,480
  • 23
  • 49
  • Good plan, but how? as ISNULL seemgly cannot be used inside the CONTAINS function - it just gives a syntax error. :( – NickG Dec 13 '13 at 13:26
  • Seems this solution isn't possible (directly), so @Dhaval's answer might be better. http://stackoverflow.com/questions/3781532/why-cant-isnull-be-called-inside-a-contains-statement – NickG Dec 13 '13 at 13:30
  • Something like this should do the trick, but admittedly, it's not very elegant: `CONTAINS((Venue.VenueName), ISNULL(@VenueName,''))` – Dan Dec 13 '13 at 13:31
  • That gives a syntax error. You can't use ISNULL inside CONTAINS. See link in my prev. comment. – NickG Dec 13 '13 at 13:32
  • Ah thanks - I did not know that. Seems like it's not that easy to rewrite the query to avoid the full-text search alltogether, when the search term is null. – Dan Dec 13 '13 at 13:39
-2

This is Perfect Answer.

Let's examine these two statements:

IF (CONDITION 1) OR (CONDITION 2)
..
IF (CONDITION 3) AND (CONDITION 4)
...
  • If CONDITION 1 is TRUE, will CONDITION 2 be checked?
  • If CONDITION 3 is FALSE, will CONDITION 4 be checked?

What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?

ADDED:
Thank to Jack for link, surprise from t-sql code:

IF  1/0 = 1 OR 1 = 1
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result
IF  1/0 = 1 AND 1 = 0
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

There is not raise a Divide by zero exception in this case.

CONCLUSION:

If C++/C#/VB has short-circuiting why can't SQL Server have it?

To truly answer this let's take a look at how both work with conditions. C++/C#/VB all have short circuiting defined in the language specifications to speed up code execution. Why bother evaluating N OR conditions when the first one is already true or M AND conditions when the first one is already false.

We as developers have to be aware that SQL Server works differently. It is a cost based system. To get the optimal execution plan for our query the query processor has to evaluate every where condition and assign it a cost. These costs are then evaluated as a whole to form a threshold that must be lower than the defined threshold SQL Server has for a good plan. If the cost is lower than the defined threshold the plan is used, if not the whole process is repeated again with a different mix of condition costs. Cost here is either a scan or a seek or a merge join or a hash join etc... Because of this the short-circuiting as is available in C++/C#/VB simply isn't possible. You might think that forcing use of index on a column counts as short circuiting but it doesn't. It only forces the use of that index and with that shortens the list of possible execution plans. The system is still cost based.

As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

GarethD
  • 68,045
  • 10
  • 83
  • 123
M Usman Shahid
  • 109
  • 6
  • It's not the "perfect answer" because all it does it does is explain that shortcutting does not work (which I'd already put in my question!. While interesting and perhaps useful, it does not in any way answer my question or solve my immediate problem. Sorry. – NickG Dec 13 '13 at 12:19