I have the following sample data for understanding the requirement.
Table:
create table ft_test
(
col1 int identity(1,1),
col2 varchar(max)
);
insert into ft_test values('John A Henry');
insert into ft_test values('Dsouza mak Dee');
insert into ft_test values('Smith Ashla Don');
Edit:
CREATE UNIQUE INDEX UIX_test_Col1 ON ft_test(col1);
CREATE FULLTEXT CATALOG ftcat
WITH ACCENT_SENSITIVITY = OFF
CREATE FULLTEXT INDEX ON ft_test
(col2 LANGUAGE 1033)
KEY INDEX UIX_test_Col1
ON ftcat
WITH STOPLIST = SYSTEM
Notes:
- Search for the given pattern in the given column of the table.
- I have more than 200 millions of records in the table.
- Created FULLTEXT index on required column.
Looking for best performing search query.
Givens:
- Pattern:
oh
- Column to Search:
col2
Expected Output:
col1 col2
----------------------
1 John A Henry
Try:
Using FULLTEXT Index:
CONTAINS
SELECT col1, col2 FROM ft_test1 WHERE CONTAINS(col2, '"*oh*"')
No output.
Using FULLTEXT Index:
FREETEXT
SELECT col1, col2 FROM ft_test1 WHERE FREETEXT(col2, '"*oh*"')
No output.
Using Pattern Index:
PATINDEX
SELECT col1, col2 FROM ft_test1 WHERE PATINDEX('%oh%',col2)>0
Got an output.
Execution Plan: Table Scan
Using character expression:
CHARINDEX
SELECT col1, col2 FROM ft_test1 WHERE CHARINDEX('oh',col2)>0
Got an output.
Execution Plan: Table Scan
Using
LIKE
.SELECT col1, col2 FROM ft_test1 WHERE col2 LIKE '%oh%'
Got an output.
Execution Plan: Table Scan