Where index index scan - index search

Question

I have a table:

CREATE TABLE Test
(
    Id int IDENTITY(1,1) NOT NULL,
    col1 varchar(37) NULL,
    testDate datetime NULL 
)

insert Test

select null
go 700000 

select cast(NEWID() as varchar(37))
go 300000

And below the indices:

create clustered index CIX on Test(ID)
create nonclustered index IX_RegularIndex on Test(col1)
create nonclustered index IX_RegularDateIndex on Test(testDate)

When I query in my table:

SET STATISTICS IO ON
select * from Test  where col1=NEWID()
select * from Test  where TestDate=GETDATE()

First, the index scan is performed, while the second index searches. I expect both of them to do an index search. Why does the first one do an index scan?

Answer 1

There is an implicit conversion generated because the function returns a value equal to datatype, and this is different from the data type declared for the column. NEWID()uniqueidentifierVARCHAR

Just try hovering over the part of the SELECTplan where there is a warning sign.

- , , , NEWID() .

- , , , ( , SCAN), FILTER, Col1 uniqueidentifier, , .

GETDATE(), datetime, , testDate, , , .