Like, charindex, patindex of sqlserver

 

Like, charindex, patindex of sqlserver

1. Environment Introduction
Test Environment sql2005
Million test data records

2. Prepare the environment
2.1 create a table
Create Table [DBO]. [depratments] (
[Dep_id] [int] not null,
[Dep_name] [varchar] (50) Collate chinese_prc_ci_as not null
) On [primary]
2.2 create data
Create procedure ins_depratments
As
Declare @ n int;
Declare @ title varchar (30 );
Set @ n = 1;
Set @ Title = '';
Begin
While @ n< 2000000
Begin
-- Set @ Title = (select case when (cast (floor (RAND () * 6) as INT) = 5 then 'department manager 'else' employee 'end );
Insert into depratments (dep_id, dep_name) values (@ n, 'developing' + Cast (@ n as varchar ));
-- Insert into employees values (@ n, 'Liu bei '+ Cast (@ n as varchar), 'male', @ title,
78000, '11110333x '+ Cast (@ n as varchar), @ n, getdate ());
Set @ n = @ n + 1;
End
End
2.3 execute exec ins_depratments

3. Scenarios
3.1 queries with percent signs
Set statistics Io on
Set statistics time on
Go
Select count (*) from depratments where dep_name like '% development 1000% ';
Go
Select count (*) from depratments where charindex ('development 100', dep_name)> 0;
Go
Select count (*) from depratments where patindex ('% development 1000%', dep_name)> 0;
Go
If no index is available, charindex> patindex> like
CPU time = 4391 milliseconds, occupied time = 5322 milliseconds.
CPU time = 3812 milliseconds, occupied time = 4690 milliseconds.
CPU time = 4047 milliseconds, occupied time = 5124 milliseconds.
Charindex> patindex> like
CPU time = 4297 milliseconds, occupied time = 4535 milliseconds.
CPU time = 3844 milliseconds, occupied time = 4024 milliseconds.
CPU time = 4219 milliseconds, occupied time = 4351 milliseconds.
Conclusion:
Percent signs (% string %) are used later. ① charindex has better performance, and like and patindex have similar performance. ② indexes are invalid in this case. 

3.2 percent number in the following query
Set statistics Io on
Set statistics time on
Go
Select count (*) from depratments where dep_name like 'development 100 ';
Go
Select count (*) from depratments where charindex ('development 100', dep_name)> 0;
Go
Select count (*) from depratments where patindex ('development 100', dep_name)> 0;
Go

If no index is available, patindex> like> charindex
CPU time = 844 milliseconds, occupied time = 1465 milliseconds.
CPU time = 3875 milliseconds, occupied time = 3914 milliseconds.
CPU time = 968 milliseconds, occupied time = 969 milliseconds.

Case with indexes like> patindex> charindex
CPU time = 0 ms, occupied time = 18 ms
CPU time = 3766 milliseconds, occupied time = 4026 milliseconds.
CPU time = 937 milliseconds, occupied time = 983 milliseconds.
Conclusion:
In the case of no index, patindex has the best performance, which is four times the charindex performance.
In the case of index, the like has the best performance.
 
Summary:
① The index is only applicable to the case after the percentage sign (string %)
② Charindex has the best performance when both sides are percent signs
③ When no index is available, the patindex has the best performance.