SQL Server Performance

CHARINDEX vs. LIKE

Discussion in 'T-SQL Performance Tuning for Developers' started by bsun, May 26, 2004.

  1. bsun New Member

    Can you please advise which where clause will give better performance.

    WHERE CHARINDEX('cos',product) <> 0

    or

    WHERE product LIKE '%cos%'


    Thanks in advance for your input.
  2. ChrisFretwell New Member

    I tried these in QA with estimated execution plans. I tried it with a field with a clustered index, a field with a non clustered index and an unindexed field on the same table, with over 1.5 million rows.
    The results were minimally different, with the charindex having slightly (.01%) better estimated performance.

    Using patindex was only .01% better than charindex.

Share This Page