Comparison of charindex/patindex/like in SQL Server _mssql2005

First, test environment:

1. Database: SQL Server 2008

2. Test table: 15000 records, char type primary key, no more index

3. Test field: ntext type, maximum data length 12000

Second, the test statement:

1. SELECT * from producttemp where productdesc like '%192.168.70.236% '

2. SELECT * from Producttemp where charindex (' 192.168.70.236 ', Productdesc) >0

3. SELECT * from Producttemp where PATINDEX ('%192.168.70.236% ', Productdesc) >0

Third, test results:

1. Efficiency test results: charindex > Like > patindex, efficiency difference is about 20%

2. Applicability Test Results:

1) charindex for the maximum displacement of the search results are required, after testing, Sql2008 in the ntext field, displacement can not exceed 3987, or return 0;

2) Patindex and like without displacement restrictions;

3) Patindex support matching expression, can apply regular;

4 like can use '%oldstring% ' for fuzzy matching;

5) charindex can only match fixed strings