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"][1]
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)
[1]: https://www.brentozar.com/archive/2019/04/index-scans-arent-always-bad-and-index-seeks-arent-always-great/
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][1] 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][2] 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][3] shows the dynamic seek shape with 38 rows qualified by the seek, but all ultimately rejected by the residual:
[![dynamic seek plan][4]][4]
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][5] shows all 1000 rows being read from the index (but again, discarded by the residual):
[![Seeking the whole table][6]][6]
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][7]).
Anyway, the message is to pay careful attention to data types.
Demo:
<>https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=03f3a7b6a081ec7a417f4bb89e59275f
[1]: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql
[2]: https://www.sql.kiwi/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html
[3]: https://www.brentozar.com/pastetheplan/?id=rJGQg9jJS
[4]: https://i.stack.imgur.com/b8iSZ.png
[5]: https://www.brentozar.com/pastetheplan/?id=B17dl9jJB
[6]: https://i.stack.imgur.com/oxplz.png
[7]: https://www.brentozar.com/pastetheplan/?id=HJeix5jkB
[8]: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=03f3a7b6a081ec7a417f4bb89e59275f
