LIKE vs CONTAINS on SQL Server

SQL Server: LIKE vs. CONTAINS - Choosing the Right Tool for Text Search

Explore the differences between SQL Server's LIKE operator and CONTAINS predicate for text searching, understanding their performance implications and best use cases.

When performing text-based searches in SQL Server, developers often encounter two primary methods: the LIKE operator and the CONTAINS predicate. While both can be used to find patterns within string data, they operate on fundamentally different principles and offer distinct advantages and disadvantages, especially concerning performance and functionality. Understanding these differences is crucial for optimizing your database queries and ensuring efficient data retrieval.

Understanding the LIKE Operator

The LIKE operator is a standard SQL feature used for pattern matching. It works by comparing a string expression to a pattern using wildcard characters. The most common wildcards are % (matches any sequence of zero or more characters) and _ (matches any single character). LIKE performs a sequential scan of the data, which can be slow on large datasets without appropriate indexing.

SELECT ProductName
FROM Products
WHERE ProductName LIKE 'SQL%'; -- Finds products starting with 'SQL'

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%Smith%'; -- Finds customers with 'Smith' anywhere in their name

SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'J_hn%'; -- Finds names like 'John', 'Jahn', etc.

Examples of using the LIKE operator with wildcards.

Understanding the CONTAINS Predicate

The CONTAINS predicate is part of SQL Server's Full-Text Search (FTS) functionality. Unlike LIKE, CONTAINS is designed for linguistic searches on large volumes of text data. It leverages full-text indexes, which are optimized for word-based searches rather than character-by-character pattern matching. FTS can perform advanced searches like proximity searches, inflectional forms (e.g., 'run', 'ran', 'running'), and weighted searches.

SELECT ArticleTitle, ArticleContent
FROM Articles
WHERE CONTAINS(ArticleContent, 'database'); -- Finds articles containing the word 'database'

SELECT ProductDescription
FROM Products
WHERE CONTAINS(ProductDescription, '"fast" OR "quick"'); -- Finds products with 'fast' or 'quick'

SELECT DocumentText
FROM Documents
WHERE CONTAINS(DocumentText, '"SQL Server" NEAR "performance"'); -- Finds 'SQL Server' near 'performance'

Examples of using the CONTAINS predicate with Full-Text Search.

Performance and Use Cases

The choice between LIKE and CONTAINS heavily depends on the specific search requirements and the volume of data. LIKE is suitable for simple pattern matching on smaller datasets or when the search pattern does not start with a wildcard. CONTAINS, backed by Full-Text Search, excels in performance for complex, linguistic searches on large text columns, as it avoids table scans by using specialized indexes.

flowchart TD
    A[Start Search] --> B{Search Type?}
    B -- 'Simple Pattern' --> C[LIKE Operator]
    C --> D{Wildcard Position?}
    D -- 'Starts with pattern' --> E[Indexable LIKE]
    D -- 'Starts with wildcard' --> F[Table Scan LIKE]
    B -- 'Linguistic/Complex Text' --> G[CONTAINS Predicate]
    G --> H{FTS Configured?}
    H -- 'Yes' --> I[Full-Text Index Search]
    H -- 'No' --> J[Error/Not Applicable]
    E --> K[Fast for small/medium data]
    F --> L[Slow for large data]
    I --> M[Very Fast for large text data]
    K & L & M --> N[End Search]

Decision flow for choosing between LIKE and CONTAINS.

Key Differences at a Glance

Here's a summary of the main distinctions between LIKE and CONTAINS:

Comparison of LIKE vs. CONTAINS in SQL Server.

In conclusion, for basic string pattern matching, especially when the pattern is fixed at the beginning, LIKE is a straightforward and often sufficient choice. However, for advanced, high-performance text searches on large bodies of text, CONTAINS with Full-Text Search is the superior solution, offering richer query capabilities and significantly better performance.