Is there a value that you can set the contains-query-string in a CONTAINS search condition so that the condition is equivalent to a constant true and which the optimizer knows is constant true?

As a comparison, LIKE '%' matches everything and the optimizer knows it, and takes that into account in the plan analysis.

I don't actually need this, and know how to code around it, but it's a nice convenience when coding user interfaces in which the user can search on various subsets of lots of criteria, and especially when doing this with ODBC's ? parameters.

asked 13 Aug '10, 00:18

Dan%20Konigsbach's gravatar image

Dan Konigsbach
455101220
accept rate: 0%


I do not believe that there is a term equivalent to what '%' does in the LIKE clause.

I think what will likely work for you is to combine the CONTAINS clause with another term which the optimizer will know definitely its value and hence can optimize out the CONTAINS clause if needed.

Example: I believe the CONTAINS predicate in the following will be optimized out of the query since 1=1 is always TRUE:

SELECT *
  FROM T 
 WHERE ( 1=1 OR CONTAINS( T.col, @variable ) )
   AND ...other-predicates...

where as it will not in the following query since 1=0 is always FALSE:

SELECT *
  FROM T 
 WHERE ( 1=0 OR CONTAINS( T.col, @variable ) )
   AND ...other-predicates...

You can also likely use a variable condition in the place of 1=0 and 1=1 as in:

SELECT *
  FROM T 
 WHERE ( @use_contains_on_col=0 OR CONTAINS( T.col, @variable ) )
   AND ...other-predicates...

and still get the required effect provided that @use_contains_on_col has a known value at statement describe and open time. I.e. set @use_contains_on_col to 1 or 0 prior to opening the cursor on your query.

Note that I have not confirmed that the above works and it may or may not work for you depending on exactly which version of SA you are using.

permanent link

answered 13 Aug '10, 02:16

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 41%

1

Just to add - host variables cannot be used as CONTAINS arguments, so connection variables - as suggested by Mark - will have to be used. CONTAINS query has to be a value known at query open time.

(16 Aug '10, 14:46) Elmi Eflov
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15
×4
×1

question asked: 13 Aug '10, 00:18

question was seen: 717 times

last updated: 13 Aug '10, 02:16