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
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)