LIKE vs. LEFT: Which Method Wins for Prefix Searches?

The Comparison

You want to find all email addresses starting with "admin":

-- Method 1: LIKE (with leading wildcard)
SELECT * FROM Users WHERE Email LIKE 'admin%';

-- Method 2: LEFT (extract first characters)
SELECT * FROM Users WHERE LEFT(Email, 5) = 'admin';

Both return the same results. But one is dramatically faster on large tables. Let's find out which.

How LIKE Works

Left-Anchored LIKE (Good)

LIKE 'prefix%' is special. SQL Server recognizes the pattern: "starts with this literal string." It can use an index seek:

-- Index on Email column is USED
SELECT * FROM Users WHERE Email LIKE 'admin%';

-- Execution plan: Index Seek on idx_email
-- Finds 'admin' in the index, scans forward until it reaches 'admio' (next letter)
-- Efficiently returns matching rows

Right-Anchored or Wildcard LIKE (Bad)

LIKE '%admin' or LIKE '%admin%' cannot use indexes:

-- Index is IGNORED
SELECT * FROM Users WHERE Email LIKE '%admin';

-- Execution plan: Table Scan
-- Must check every row because the prefix is unknown

How LEFT Works

LEFT(column, n) extracts the first n characters. The function is applied to every row:

-- Index is IGNORED (function on indexed column)
SELECT * FROM Users WHERE LEFT(Email, 5) = 'admin';

-- Execution plan: Table Scan
-- For each row: calculate LEFT(Email, 5), then compare to 'admin'
-- Function evaluation disables the index

Remember: functions on indexed columns disable index seeks.

Performance Comparison

Method	Syntax	Index Usage	Rows Examined (1M table)	Time (approx)
LIKE 'prefix%'	`WHERE Email LIKE 'admin%'`	✓ Index Seek	50 (estimate)	5 ms
LEFT()	`WHERE LEFT(Email, 5) = 'admin'`	✗ Table Scan	1,000,000	500 ms
Difference	—	Index vs. Scan	20,000x difference	100x slower

Why LIKE Wins

Index-Aware Pattern Recognition

SQL Server's optimizer has special logic for LIKE:

LIKE 'admin%' → "Starts with literal" → Can use index
LIKE '%admin' → "Ends with literal" → Can't use index
LIKE '%admin%' → "Contains literal" → Can't use index

Only left-anchored patterns (no wildcard at the start) can use indexes.

How the Index Helps

-- Index on Email (sorted):
-- 'alice@example.com'
-- 'admin@company.com'
-- 'admin.user@company.com'
-- 'admired@site.com'
-- 'bob@example.com'
-- ...

-- Query: WHERE Email LIKE 'admin%'
-- Binary search finds 'admin'
-- Scans forward to 'admin...'
-- Stops at 'admired' (next prefix letter 'e' > 'd')
-- Returns ~50 rows out of 1 million

Why LEFT Can't Use the Index

LEFT() is a function. SQL Server must evaluate it for every row:

-- For each row:
-- 1. Calculate LEFT(Email, 5)
-- 2. Compare to 'admin'
-- 3. If match, include in results

-- The index can't help because the index stores full Email values,
-- not their first-5-character substrings

Real Examples

Example 1: Email Prefix Search

-- Table: Users (10 million rows)
-- Index: idx_email ON Email

-- LIKE (GOOD - Index Seek)
SELECT * FROM Users WHERE Email LIKE 'admin%';
-- Logical reads: 50
-- Time: 5 ms

-- LEFT (BAD - Table Scan)
SELECT * FROM Users WHERE LEFT(Email, 5) = 'admin';
-- Logical reads: 50,000 (entire table)
-- Time: 500 ms
-- 100x slower!

Example 2: Product Code Prefix

-- Table: Products (5 million rows)
-- Index: idx_code ON ProductCode

-- LIKE (GOOD)
SELECT * FROM Products WHERE ProductCode LIKE 'PROD-%';
-- Finds all products starting with 'PROD-'
-- Fast index seek

-- LEFT (BAD)
SELECT * FROM Products WHERE LEFT(ProductCode, 5) = 'PROD-';
-- Table scan, slow

Edge Cases and Gotchas

Case Sensitivity

If your database uses a case-sensitive collation, both methods are case-sensitive:

-- Case-sensitive collation
COLLATE SQL_Latin1_General_CS_AS

-- LIKE is case-sensitive
WHERE Email LIKE 'Admin%'  -- Matches 'Admin...', not 'admin...'

-- LEFT is also case-sensitive
WHERE LEFT(Email, 5) = 'Admin'  -- Matches 'Admin', not 'admin'

If you need case-insensitive search, make it explicit:

-- Case-insensitive LIKE (still uses index in most cases)
WHERE UPPER(Email) LIKE 'ADMIN%';

-- Case-insensitive LEFT (disables index, bad idea)
WHERE UPPER(LEFT(Email, 5)) = 'ADMIN';

Variable Prefix Length

If the prefix length is dynamic, you must use LEFT or SUBSTRING:

-- Can't use LIKE with dynamic length
DECLARE @PrefixLen INT = 5;
SELECT * FROM Users WHERE Email LIKE 'admin%';  -- Can't parameterize the wildcard

-- Must use LEFT
SELECT * FROM Users WHERE LEFT(Email, @PrefixLen) = 'admin';

In this case, LEFT is your only option, but know the performance cost.

LIKE with Escape Characters

If your data contains wildcard characters (%, _), LIKE requires escape characters:

-- Search for email starting with 'admin_user'
-- The underscore is a wildcard in LIKE, so escape it
WHERE Email LIKE 'admin\_user%' ESCAPE '\';

-- LEFT avoids this complexity
WHERE LEFT(Email, 10) = 'admin_user';

Why Developers Use LEFT Instead

Readability: LEFT(Email, 5) = 'admin' is more explicit about the intent
Dynamic lengths: LEFT(Email, @Length) works; LIKE patterns don't parameterize well
Familiarity: Some developers learned string functions first, LIKE second
Escape characters: LIKE requires escaping %, _, and [; LEFT doesn't
Consistency: Using the same function across code (even if slow)

When to Use Each

Scenario	Use LIKE?	Use LEFT?
Prefix search (static prefix)	✓ Yes (fastest)	No
Prefix search (dynamic length)	No (can't parameterize)	✓ Yes (only option)
Contains substring ('%admin%')	✓ LIKE (standard)	No (very slow)
Ends with pattern ('admin%')	✓ LIKE (can use index)	No
Data with %, _ characters	Yes (with ESCAPE)	✓ Yes (simpler)

Best Practices

1. Prefer LIKE for Static Prefix Searches

-- Good
SELECT * FROM Users WHERE Email LIKE 'admin%';

2. If You Need Dynamic Length, Consider a Computed Column

-- Create computed column
ALTER TABLE Users ADD EmailPrefix5 AS LEFT(Email, 5) PERSISTED;

-- Create index
CREATE INDEX idx_email_prefix ON Users(EmailPrefix5);

-- Now query efficiently
SELECT * FROM Users WHERE EmailPrefix5 = @Prefix;

3. Benchmark Before Deciding

Always check execution plans on your actual data and server:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test both methods on large table
SELECT COUNT(*) FROM Users WHERE Email LIKE 'admin%';
SELECT COUNT(*) FROM Users WHERE LEFT(Email, 5) = 'admin';

-- Compare logical reads and CPU time

4. Document the Intent

-- If using LEFT for dynamic length, explain why
-- (LIKE doesn't support dynamic pattern length)
SELECT * FROM Users WHERE LEFT(Email, @PrefixLen) = @Prefix;

Real-World Impact

Migration Story

A company had an auto-complete feature that was slow:

-- Original (slow)
SELECT TOP 10 Email FROM Users
WHERE LEFT(Email, LEN(@SearchTerm)) = @SearchTerm
ORDER BY Email;

On 10 million rows, each keystroke took 500 ms. Users complained.

Fix:

-- Refactored (fast)
SELECT TOP 10 Email FROM Users
WHERE Email LIKE @SearchTerm + '%'
ORDER BY Email;

Result: 500 ms → 10 ms per keystroke. Autocomplete felt instant.

The Verdict

For prefix searches, LIKE 'prefix%' wins. It uses indexes; LEFT() doesn't. Use LEFT only when you need dynamic prefix length, and be aware of the performance cost.

Remember:

LIKE 'prefix%' = Index Seek (fast)
LEFT(column, n) = 'prefix' = Table Scan (slow)
For dynamic length, use LEFT and accept the cost, or create a computed column index
Always verify with execution plans

← Back to Blog