Where Clause Index Scan - Index Seek
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 &amp; A concerning the number of times the NEWID function is evaluated:

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