This post explains about a different behavior with search for UNIQUEIDENTIFIER datatype column in SQL Server.

If you would have worked with UNIQUEIDENTIFIER before, you would have come across this scenario earlier. But for those not, let us see the scenario.

CREATE TABLE TEST (COLUMN1 UNIQUEIDENTIFIER)
INSERT INTO TEST VALUES(‘F7ABC3AA-0534-44F3-BDBB-0011CEFB6993′)

–Search a value in the column by adding “extra” characters
SELECT * FROM TEST WHERE COLUMN1=’F7ABC3AA-0534-44F3-BDBB-0011CEFB6993.XML’

You will expect the search will not return any data as it can not find the exact match, however, it returns results!!!

What is reason for this strange behaviour?

No, this is not a strange behaviour, its an intended and default behaviour in SQL Server for UNIQUEIDENTIFIER.

Let us look at what happens behind the scene.First step is enable the execution plan and execute the SELECT query.

As per the execution plan, SQL Server does an Implicit conversion of the value to uniqueidentifier before the search happens.As a result, only first 36 characters is being used for the search condition and returns the results.Any characters more than 36, would be ignored in the search condition.

For completeness, Please note that if we are passing a value that can not be converted as unique identifier, it will throw an exception as below.

SELECT * FROM TEST WHERE COLUMN1=’F7ABC3AA-0534-44F3-BDBB-0011CEFB699′

Msg 8169, Level 16, State 2, Line 5
Conversion failed when converting from a character string to uniqueidentifier.

Hope you enjoyed this post, have your thoughts on the same.