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.
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
try this..
SELECT
*
FROM
MetaDataTag
WHERE PATINDEX('SUN RCC%', Name ) != 0
(select *) ?that would take timeCHARINDEX(See: technet.microsoft.com/en-us/library/ms186323.aspx) if the returned value is greater than 0 it exists in the string. Unfortunately asCHARINDEX('123','1')andCHARINDEX('123','5')both return 0 it can't quite determine whether the contained string is either the start or doesn't exist. You could work around this by adding a prefix to the string being compared but personally I'd say in this instanceLIKEis a reasonable solution.LEFT(str)(See: stackoverflow.com/questions/9493844/…)LIKEhas poor performance in general? If the latter, it's simply not true; assuming that you have an index that supports searching on the Name column, the optimizer can use that index to satisfy your query since you don't have a wildcard at the beginning of your pattern. If the former, check to make sure that you have a covering index for your query.