Which is faster CHARINDEX or LIKE ?????
-
Which is faster CHARINDEX or LIKE ?????
where fld_description is nvarchar(500)
where fld_description like '%HOLD%'
or
where charindex('hold',fld_description) > 0
-
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.
-
thanks.. thats what i thought but wasnt sure..
-
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 -
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?
-
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] -
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 + '%'
-
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] -
That clears it up... obviously works now on another table.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply