Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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.
share|improve this answer
1  
+1 nice one Mikael –  Aaron Bertrand Mar 8 '13 at 14:21

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.
share|improve this answer

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:

BOL entry extract

The example referred to is:

BOL example

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';

Index seek on uuid

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.