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)