Unique Identifier with Extra Characters Still Matching in Select

Question

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.

Answer 1

The implicit conversion also works if the value is enclosed in curly brackets {...}.

If you add those in the query the implicit conversion will fail if the original value is too long because the last } ends up in the wrong place.

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

If you try the convert

SELECT CONVERT(UNIQUEIDENTIFIER, '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'+'}');

you get

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

Answer 2

The additional characters are simply ignored (well, silently truncated) by SQL Server during implicit conversion. For example:

SELECT CONVERT(UNIQUEIDENTIFIER, '7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS');

Result:

------------------------------------
7DA26ECB-D599-4469-91D4-F9136EC0B4E8

This is not unlike this scenario:

DECLARE @x VARCHAR(1) = 'xyz';
SELECT @x;

Result:

----
x  

You can not configure this but if you want your variable to fail conversion then you can try to stuff the variable into a table with CHAR(36) first, which will fail due to truncation:

DECLARE @x TABLE(y CHAR(36));
INSERT @x SELECT '7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS';

Result:

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

Answer 3

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.