Index Seek scanning whole table dependent on parameter value

Question

I have a query:

SELECT  Id, 
        ColumnA,
        ColumnB
FROM    MyTable
WHERE   ColumnA = @varA OR
        ColumnB = @varB  

The table is defined as

CREATE TABLE MyTable
(
    Id INT IDENTITY(-2147483648,1) PRIMARY KEY,
    ColumnA VARCHAR(22)
    ColumnB VARCAHR(22)
)

and there is a non clustered index on the table

CREATE INDEX IX_MyIndex ON MyTable
(
    ColumnA
)

When I run the query with the parameters below:

DECLARE @varA nvarchar(4000) = ''
DECLARE @varB  nvarchar(8) = '10140730'

The execution plan shows an index seek on IX_MyIndex, however it shows number of rows read as 17million rows but actual number of rows as 0 (There are 0 rows in MyTable.ColumnA with the value '') If I turn SET STATISTICS IO ON I can see the full table is being read

This makes sense as per: this article in the section "Here’s a “bad” index seek"

However, when I run the same query with the parameters:

DECLARE @varA nvarchar(8) = 'a'
DECLARE @varB  nvarchar(8) = '10140730'

The seek operator doesn't have a "number of rows read" property (there are 0 rows MyTable.ColumnA in with value 'a') and SET STATISTICS IO reports single figure logical reads

Incidentally, the plan has an implicit convert warning and the issue goes away when I change the query like so:

SELECT  Id, 
        ColumnA,
        ColumnB
FROM    MyTable
WHERE   ColumnA = CONVERT(VARCHAR(22),@varA) OR
        ColumnB = CONVERT(VARCHAR(22),@varB)

Or change the underlying column to NVARCHAR

However, I am curious as to why the behaviour of the index seek with the two different values for @varA is different even though both of them return the same number of records in the table (0)

Answer 1

When there is a mismatch between the data types of the column and variable, SQL Server cannot directly use the seeking ability of a b-tree index to locate the correct range of values.

When the rules of data type precedence mean that the column data would have to be converted to the data type of the variable, this would mean scanning the whole table or index, converting each value and testing it against the variable as a residual predicate.

This is obviously not ideal, but so common (unfortunately) that SQL Server has a built-in way to achieve an index seek in these cases. It takes the supplied value and computes the range of values it maps to, accounting for the type conversion and collation.

This feature is known as a dynamic seek and the internal method that computes the mapped range is called GetRangeThroughConvert.

For example, when the nvarchar variable contains 'a', the mapped range of values for data type varchar might be 'a' to 'B' (the exact range depends on the collation). This means SQL Server can seek the varchar index between 'a' and 'B', testing only the matches for equality with 'a' (as nvarchar) as a residual predicate.

When the supplied value is the empty string, the computed range is infinite, so the whole index is effectively scanned.

For example:

DROP TABLE IF EXISTS dbo.MyTable;
GO
CREATE TABLE dbo.MyTable
(
    ColumnA varchar(22) COLLATE Latin1_General_CI_AS NOT NULL
);
GO
INSERT dbo.MyTable 
    WITH (TABLOCKX)
    (ColumnA)
SELECT TOP (1000)
    REPLICATE(CHAR(65 + ROW_NUMBER() OVER (ORDER BY @@SPID) % 26), 22)
FROM master.sys.all_columns AS AC1
CROSS JOIN master.sys.all_columns AS AC2;
GO
CREATE INDEX IX_MyIndex_A ON dbo.MyTable(ColumnA);

The following query uses a dynamic seek with a range of 'a' to 'B' and a residual predicate of CONVERT_IMPLICIT(nvarchar(22),[dbo].[MyTable].[ColumnA],0)=[@varA]:

DECLARE @varA nvarchar(22) = N'a';

SELECT MT.ColumnA 
FROM dbo.MyTable AS MT 
WHERE MT.ColumnA = @varA;

The execution plan shows the dynamic seek shape with 38 rows qualified by the seek, but all ultimately rejected by the residual:

The 38 rows are those counted by the query:

SELECT COUNT_BIG(*)
FROM dbo.MyTable AS MT 
WHERE MT.ColumnA > 'a' 
AND MT.ColumnA < 'B';

When the variable contains an empty string, the calculated range is unbounded so the seek effectively scans the whole index:

DECLARE @varA nvarchar(22) = N''; -- empty string

SELECT MT.ColumnA 
FROM dbo.MyTable AS MT 
WHERE MT.ColumnA = @varA;

The execution plan shows all 1000 rows being read from the index (but again, discarded by the residual):

The empty string is a special case where GetRangeThroughConvert cannot produce a useful range. A single space character does produce a narrow seek range (plan).

Anyway, the message is to pay careful attention to data types.

db<>fiddle

Answer 2

Returning the same number of rows is different from estimating the number of rows that will be returned. The IMPLICIT_CONVERSION() warning present in the query plan is a warning that the cardinality estimates may be incorrect.

The resolution, as you know, is to define the table and variables precisely the same way, and in the case of nvarchar/nchar types, to specify constant values using the N'...' notation.

This is a guess, but the zero-length-string hidden behind an implicit conversion sufficiently confuses the query optimizer such that it has no idea how many rows will match.

You've identified another cause of the incorrect estimate, in your statement "There are 0 rows in MyTable.ColumnA with the value ''". Since there are no rows with a zero-length-string value, the statistics histogram for that column will not help get a good estimate. If you insert a single row into the table with a zero-length-string value for that column, and update statistics on the column, you'll probably see a different cardinality estimate.

It doesn't really matter much for variable assignments, but you should change that part of your code to:

DECLARE @varA nvarchar(8) = N'a';
DECLARE @varB nvarchar(8) = N'10140730';

Note that I've wrapped the constants in N'...'. Also of note is that you should always end every statement in T-SQL with a semicolon. Search this list of deprecated features for "semicolon"; you'll find that leaving out the semi-colon is deprecated.

While I'm beating the nitpick drum, types in T-SQL are lower-case. Look at the output from SELECT * FROM sys.types; you'll see SQL Server lists them as lower case.