Where Clause Index Scan - Index Seek

I have below table:

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

GO

insert Test    
select null
go 700000 

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

And below indexes:

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 on my table:

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

First is making index scan whereas the second index seek. I expect that both of them must make index seek. Why does the first make index scan?

The problem here is an implicit conversion to UNIQUEIDENTIFIER type. There is a warning about it in the execution plan, and it is connected with Data Type Precedence.

Your table is using the wrong data type to store GUIDs. If col1 were correctly typed as uniqueidentifier, your problem would never have arisen. The string representation of a GUID has a maximum length of 36 (not 37!) characters, which is much less efficient than using uniqueidentifier (16 bytes). Note also that NEWID() returns a uniqueidentifier, not any sort of string.

I added some hints to my queries below to get an identical execution plan.

You can avoid this if you assign NEWID() to a variable but you still have to declare VARCHAR(37) variable or just cast NEWID() to VARCHAR(37) in the where clause.

Here are a few queries that show us how it works:

-- Query 1
SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=NEWID()
GO

-- Query 2
DECLARE @id UNIQUEIDENTIFIER = NEWID()

SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=@id
GO

-- Query 3
DECLARE @id VARCHAR(37) = NEWID()

SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=@id
GO

-- Query 4
SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=cast(NEWID() as varchar(37))
GO

Related Q & A concerning the number of times the NEWID function is evaluated:

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

StackExchange Database Administrators Q#157042, answer score: 7