LIKE Cardinality: String Statistics and Row Estimates

LIKE cardinality describes how many rows SQL Server expects a pattern to match. A useful access path and an accurate estimate answer different questions. Compare estimated and actual matching rows before drawing a conclusion from the plan.

Start LIKE cardinality with a known distribution

The setup block below creates a temporary table with 10,000 patterned names, one empty string and one NULL. Its known groups distinguish prefix, contains and suffix matches, so you know the exact matches before any estimate is interpreted.

The column uses nvarchar(80) with an explicit case-insensitive collation. The parameter also uses nvarchar(80). The setup block creates a name index and updates its statistics using FULLSCAN. Record the engine build and database compatibility alongside the plans.

DROP TABLE IF EXISTS #Names;
CREATE TABLE #Names(Id int NOT NULL PRIMARY KEY, Name nvarchar(80) COLLATE Latin1_General_100_CI_AS NULL);

;WITH D(n) AS (SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)),
N(n) AS (SELECT a.n+10*b.n+100*c.n+1000*d.n FROM D AS a CROSS JOIN D AS b CROSS JOIN D AS c CROSS JOIN D AS d)
INSERT #Names
SELECT n, CASE n%4
 WHEN 0 THEN N'A'+CONVERT(nvarchar(8),n)+N'Z'
 WHEN 1 THEN N'Z'+CONVERT(nvarchar(8),n)+N'A'
 WHEN 2 THEN N'Z'+CONVERT(nvarchar(8),n)+N'A'+CONVERT(nvarchar(8),n)+N'Z'
 ELSE N'Z'+CONVERT(nvarchar(8),n)+N'Z' END
FROM N;
INSERT #Names VALUES(10000,NULL),(10001,N'');

CREATE INDEX IX_Names_Name ON #Names(Name);
UPDATE STATISTICS #Names IX_Names_Name WITH FULLSCAN;

-- The known groups: 2,500 prefix matches, 7,500 contains matches, 2,500 suffix matches.
SELECT SUM(CASE WHEN Name LIKE N'A%' THEN 1 ELSE 0 END) AS PrefixMatches,
 SUM(CASE WHEN Name LIKE N'%A%' THEN 1 ELSE 0 END) AS ContainsMatches,
 SUM(CASE WHEN Name LIKE N'%A' THEN 1 ELSE 0 END) AS SuffixMatches,
 COUNT_BIG(*) AS TotalRows
FROM #Names;

SELECT Id, Name FROM #Names WHERE Name LIKE N'A%';
SELECT Id, Name FROM #Names WHERE Name LIKE N'%A%';
SELECT Id, Name FROM #Names WHERE Name LIKE N'%A';

Read the string summary behind LIKE cardinality

The statistics header reports whether a string index exists. This string summary supports LIKE selectivity estimates. It is distinct from the histogram on the statistics object’s first key column.

DBCC SHOW_STATISTICS(N'tempdb..#Names', N'IX_Names_Name')
WITH STAT_HEADER;

Read the exact header your instance returns. A YES flag does not prove every pattern estimate is accurate. Different distributions and compilation information still matter.

Observed LIKE cardinality estimates and matching rows

SQL Server 2025 build 17.0.5005.3 returned these results with master and tempdb at compatibility level 170. The plans used cardinality-estimation model 170.

The comparison uses the matching output operator, which executed once in each serial plan.

The statistics header reported 10,002 rows, 10,002 sampled rows, 169 steps and a string index. The plans came from the same table with STATISTICS XML enabled around the five matching-row probes.

The UNKNOWN plan returned matching rows through Nested Loops. Its estimate was lower than the actual prefix matches. These results apply to this demo table and compilation context, not every production distribution.

Compare matching-row estimates at the correct operator

Enable the actual execution plan and inspect the operator that returns the matching rows. Record estimated and actual rows there. A COUNT aggregate can output one row even when its input matched thousands.

Use the same data and types for each comparison. The probes are ordinary SELECT statements rather than a count-only plan.

Control the compilation information for LIKE cardinality

OPTIMIZE FOR UNKNOWN deliberately hides the pattern value from the estimate. RECOMPILE supplies a contrasting compilation using the current value. Neither test justifies a universal percentage for all unknown patterns.

EXEC sys.sp_executesql
 N'SELECT Id,Name FROM #Names WHERE Name LIKE @Pattern
 OPTION(OPTIMIZE FOR(@Pattern UNKNOWN));',
 N'@Pattern nvarchar(80)', @Pattern=N'A%';
EXEC sys.sp_executesql
 N'SELECT Id,Name FROM #Names WHERE Name LIKE @Pattern
 OPTION(RECOMPILE);',
 N'@Pattern nvarchar(80)', @Pattern=N'A%';

DROP TABLE #Names;

Treat RECOMPILE as a controlled comparison, with its compilation cost considered separately. A different production distribution can produce different estimates. Keep the saved plans, statistics header and actual matches together.

Check the actual rows at the right operator, and the estimate explains itself.

A row estimate is not proof of a good plan, it is a guess to verify.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.