1

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:

  1. Search for the given pattern in the given column of the table.
  2. I have more than 200 millions of records in the table.
  3. Created FULLTEXT index on required column.

Looking for best performing search query.

Givens:

  1. Pattern: oh
  2. Column to Search: col2

Expected Output:

col1    col2
----------------------
1       John A Henry

Try:

  1. Using FULLTEXT Index: CONTAINS

    SELECT col1, col2
    FROM ft_test1
    WHERE CONTAINS(col2, '"*oh*"')  
    

No output.

  1. Using FULLTEXT Index: FREETEXT

    SELECT col1, col2
    FROM ft_test1
    WHERE FREETEXT(col2, '"*oh*"')  
    

No output.

  1. Using Pattern Index: PATINDEX

    SELECT col1, col2
    FROM ft_test1
    WHERE PATINDEX('%oh%',col2)>0
    

Got an output.

Execution Plan: Table Scan

  1. Using character expression: CHARINDEX

    SELECT col1, col2
    FROM ft_test1
    WHERE CHARINDEX('oh',col2)>0
    

Got an output.

Execution Plan: Table Scan

  1. Using LIKE.

    SELECT col1, col2
    FROM ft_test1
    WHERE col2 LIKE '%oh%'   
    

Got an output.

Execution Plan: Table Scan

MAK
  • 6,824
  • 25
  • 74
  • 131
  • 2
    You should read this Paul White's article - [Trigram Wildcard String Search in SQL Server](https://sqlperformance.com/2017/09/sql-performance/sql-server-trigram-wildcard-search) – Andrey Nikolov Dec 27 '18 at 07:56
  • 1
    I think fulltext should give you the best results since all other options are non-seargable. I haven't worked with fulltext for a very long time but you should be able to get the results you want. Perhaps try to change the search pattern. Try `CONTAINS(col2, 'oh')` – Zohar Peled Dec 27 '18 at 08:25
  • @ZoharPeled is correct. You don't use wildcards with `CONTAINS`. Wildcard operators are for functions like `LIKE` and `PATINDEX`. [CONTAINS (Transact-SQL) - Using CONTAINS with ](https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017#a-using-contains-with-simpleter). – Thom A Dec 27 '18 at 09:11
  • (the same is also true for `FREETEXT`: [FREETEXT (Transact-SQL) - Using FREETEXT to search for words containing specified character values](https://learn.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql?view=sql-server-2017#a-using-freetext-to-search-for-words-containing-specified-character-values).) – Thom A Dec 27 '18 at 09:19
  • 2
    I'm voting to close this question as off-topic because it was asked again here: https://dba.stackexchange.com/questions/226318/query-performance-of-searching-pattern – Aaron Bertrand Jan 04 '19 at 15:41

0 Answers0