March 18, 2003 at 6:06 am
Hi
I noticed the other day that if you write a select statement such as:
SELECTItemID,
Version
FROMItem
WHERECONTAINS(*,@Criteria)
AND1=2
the query analyzer trace returns 0 reads, and the execution plan
indicates a constant scan and nothing else (ie. nothing's happening).
However, if you use a variable in this:
declare @switches int
set @switches = 2
SELECTItemID,
Version
FROMItem
WHERECONTAINS(*,@Criteria)
AND@switches&1=1
the trace and execution plan indicate that the table gets hit, and then the
results chucked out afterwards.
I was wondering if it is possible to use variables in this type of situation
to have sql server ignore some selects, e.g. if you wanted to do a union
SELECTItemID,
Version
FROMTable_1
WHERECONTAINS(*,@Criteria)
AND@switches&1=1
UNION
SELECTItemID,
Version
FROMTable_2
WHERECONTAINS(*,@Criteria)
AND@switches/2&1=1
The full text indexing is still very quick even with a few unions, but it
seemed a much nicer idea that you might be able to just turn off
different bits of the query.
Does anyone have any ideas?
Many Thanks
Jon
March 18, 2003 at 10:00 am
If you selectively turn off some queries, then you start to lose the benefits of query plan caching. I would think that you would want to build separate queries and stored procedures to call based on the parameters that are passed in.
Steve Jones
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply