Which is faster CHARINDEX or LIKE ?????

Question

Which is faster CHARINDEX or LIKE ?????

kingram-15027

Ten Centuries

Points: 1074
More actions
May 31, 2005 at 9:43 am

#76324

Which is faster CHARINDEX or LIKE ?????
where fld_description is nvarchar(500)

where fld_description like '%HOLD%'
or
where charindex('hold',fld_description) > 0

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Should be the same speed... with the wildcard at the front of the like, a scan is required to find all possible matches. Using charindex will always result in a scan. However if you where to do like 'abc%', this could result in an index seek. Again however, if you were to do something like this in a proc you'd get a scan again :

where somecol like @SomeParam + '%'

This is because the optimizer must take into consideration that the parameter could containt a wildcard at the beginning of the string.

kingram-15027 Ten Centuries Points: 1074 More actions · Answer 2

kingram-15027

Ten Centuries

Points: 1074

May 31, 2005 at 10:28 am

#561952

thanks.. thats what i thought but wasnt sure..

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 3

Just an answer to Rémi's comment:

SQLserver will still use the index if you have this kind of query:

declare @v-2 varchar(50)

set @v-2 = 'E'

select * from dbo.T_CUS_customer

where cus_code_char like @v-2 + '%'

StmtText

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[LHC].[dbo].[T_CUS_customer]))

|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1009], [Expr1010], LikeRangeInfo([@v]+'%', NULL)))

|--Compute Scalar(DEFINE[Expr1009]=Convert(LikeRangeStart([@v]+'%', NULL)), [Expr1010]=Convert(LikeRangeEnd([@v]+'%', NULL)), [Expr1007]=LikeRangeInfo([@v]+'%', NULL)))

| |--Constant Scan

|--Index Seek(OBJECT[LHC].[dbo].[T_CUS_customer].[IX_1_CUS_code_char]), SEEK[T_CUS_customer].[cus_code_char] > [Expr1009] AND [T_CUS_customer].[cus_code_char] < [Expr1010]), WHERElike([T_CUS_customer].[cus_code_char], [@v]+'%', NULL)) ORDERED FORWARD)

and if you are using a clustered index you can even avoid the bookmark lookup:

declare @v-2 varchar(50)

set @v-2 = 'E'

select * from dbo.T_CUS_customer

where cus_name like @v-2 + '%'

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1009], [Expr1010], LikeRangeInfo([@v]+'%', NULL)))

|--Compute Scalar(DEFINE[Expr1009]=Convert(LikeRangeStart([@v]+'%', NULL)), [Expr1010]=Convert(LikeRangeEnd([@v]+'%', NULL)), [Expr1007]=LikeRangeInfo([@v]+'%', NULL)))

| |--Constant Scan

|--Clustered Index Seek(OBJECT[LHC].[dbo].[T_CUS_customer].[I_C_CUS]), SEEK[T_CUS_customer].[cus_name] > [Expr1009] AND [T_CUS_customer].[cus_name] < [Expr1010]), WHERElike([T_CUS_customer].[cus_name], [@v]+'%', NULL)) ORDERED FORWARD)

Bye
Gabor

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I can't replicate that index seek here..

Can you post the ddl and some sample data so I can understand what I'm doing wrong?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Watch Gabor's code

declare @v-2 varchar(50)

set @v-2 = 'E'

select * from dbo.T_CUS_customer

where cus_code_char like @v-2 + '%'

There is no wildcard at the beginning, only at the end. So, if there is an index on that cus_code_char column, it's likely to be used.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I know that but this query results in a scan :

Declare @name as varchar(10)

set @Name = 'id'

Select * from dbo.SysColumns where name like @Name + '%'

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

There isn't a separate index on name. This column is at second position in another nonclustered indexes. So, I would say, this is somewhat expected behaviour.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

That clears it up... obviously works now on another table.