Unique Identifier with Extra Characters Still Matching in Select

We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.

For example:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8'

returns the row with uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

But if you run:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'

it also returns the row with the uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

SQL Server seems to ignore all characters beyond the 36 when doing its selects. Is this a bug/feature or something that can configured?

It's not a massive issue as we have validation on the front end for the length but it doesn't seem correct behaviour to me.

SQL Server seems to ignore all characters beyond the 36 when doing its selects. Is this a bug/feature or something that can configured?

The behaviour is documented in the Books Online entry for the uniqueidentifier type:

The example referred to is:

That being said, I prefer to avoid the implicit conversions. A uniqueidentifier literal can be typed directly in T-SQL using ODBC escape syntax:

DECLARE @T AS TABLE
(
    uuid uniqueidentifier UNIQUE NOT NULL
);

INSERT @T (uuid)
SELECT {guid '{7DA26ECB-D599-4469-91D4-F9136EC0B4E8}'};

SELECT t.uuid 
FROM @T AS t 
WHERE 
    t.uuid = {guid '{7DA26ECB-D599-4469-91D4-F9136EC0B4E8}'};

This is the same syntax SQL Server uses internally in execution plans when constant-folding a string representation to a typed uniqueidentifier:

SELECT t.uuid 
FROM @T AS t 
WHERE 
    t.uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8';

Whether you can pass typed uniqueidentifiers to and from SQL Server may depend on the library you are using, but 36-character strings strikes me as the least desirable of the available options. If you must perform conversions, make them explicit, and use a 16-byte binary value instead of a string.

StackExchange Database Administrators Q#36244, answer score: 11