CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-performancesql-like
SESHI
SESHI

Reputation: 33

Avoiding using LIKE in SQLQueries

What can I use instead of LIKE in the queries? As LIKE has poor performance impact on the search condition.

My scenario:

SELECT 
    *
FROM 
    MetaDataTag
WHERE 
    Name LIKE 'SUN RCC%'

I cant use contains as a Full text index is required on the table, which I am not opting for.

Suggestions will be very helpful.

Upvotes: 1

Views: 925

Answers (2)

RobertKing
RobertKing

Reputation: 1921

try this..

SELECT 
    *
FROM 
    MetaDataTag
WHERE PATINDEX('SUN RCC%', Name ) != 0

Upvotes: 0

Markus Winand
Markus Winand

Reputation: 8746

In your particular case, LIKE is not a bad option because you are only using a postfix wildcard. Your query can actually use an index on the Name column.

Have a look at this visual explanation why it works: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

Upvotes: 7

Related Questions