SQLServer: Search where on uniqueidentifier guid-CodePudding

How do I search for a unique identifier guid in SQLServer table, without doing table scans? Should I just use string or cast as uniqueidentifier? I see different examples on internet.

SELECT * FROM dbo.Product
where productGuid = '321F6C1D-3B28-4880-B1ED-434D7AE299D8'

SELECT * FROM dbo.Product
where productGuid = cast('321F6C1D-3B28-4880-B1ED-434D7AE299D8' as uniqueidentifier)

When doing text string search, I still saw seeks in execution plan. Should I cast as uniqueidentifier to be safe, since we are using Java and JDBCTemplate?

CodePudding user response：

This

SELECT * FROM dbo.Product
where productGuid = '321F6C1D-3B28-4880-B1ED-434D7AE299D8'

Is fine because UNIQUEIDENTIFIER has a higher Data Type Precedence than VARCHAR. So the VARCHAR literal is converted to a UNIQUEIDENTIFIER and the index can be used.

The case you have to look out for is where the literal or parameter has a higher precedence than the table column, which causes all the column values to be converted to the literal or parameter type for comparison, and will (generally*) prevent index use.

*there are some special cases where the comparison avoids the column-side conversion.

CodePudding user response：

If using it in this format (GUID format), the correct way would be:

SELECT * FROM dbo.Product
WHERE productGuid = CAST('321F6C1D-3B28-4880-B1ED-434D7AE299D8' AS UNIQUEIDENTIFER)