CODEWITHSUNDEEP
sqlsql-serverfull-text-search
Seph
Seph

Reputation: 8693

query with CONTAINS on table without full text index

Without using dynamic SQL, and other than an If ELSE is there any way to test if a table has a full text index without causing additional locking.

I'm looking for some way that I can still execute:

SELECT * FROM Cars WHERE <not has full text index> OR (<has full text index> AND CONTAINS(*, 'fast'))

There is no full-text index on this the table Cars ideally I would like for it to just return all rows when we try and specify the contains string.

What I have currently from SQL is:

SELECT * FROM Cars
WHERE NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('Cars') AND COLUMNPROPERTY(object_id, name, 'IsFulltextIndexed') = 1)
OR (EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('Cars') AND COLUMNPROPERTY(object_id, name, 'IsFulltextIndexed') = 1)
AND CONTAINS(*, 'fast'))

Which parses fine but fails because:

Msg 7601, Level 16, State 1, Line 3 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Cars' because it is not full-text indexed.

Is there a way to do this without dynamic SQL and without using an IF ELSE?

Bonus points if you can explain why the optimizer doesn't short-circuit the 1=0 and CONTAINS from SELECT * FROM Cars WHERE 1=1 OR (1=0 AND CONTAINS(*, 'fast')).

In practice I will likely create a new class attribute and then use that to ignore the full text indexes in my ORM, but I am interested to see if there is another option.

Upvotes: 1

Views: 4060

Answers (1)

MatBailie
MatBailie

Reputation: 86716

No.

All single queries are compiled into a single plan, and so all the individual components must always be valid. It is not possible to write a single statement where different components are valid in different circumstances.

To do so require more traditional programatic constructs. These are available in T-SQL, but not within a single SQL statement. As such, the solutions you have listed as innapropriate are actually the ways to resolve such issues.

Upvotes: 1

Related Questions