THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser uniqueidentifier

Create this table

CREATE TABLE #bla (SomeVal uniqueidentifier)
INSERT #bla VALUES('D903D52D-DBFA-4904-9D95-F265152A391F')



What do you think this will return?

SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'
UNION ALL
SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F1'

Surprised?

What about this?

SELECT * FROM #bla
WHERE SomeVal = CONVERT(uniqueidentifier,'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')
Published Tuesday, May 27, 2008 3:37 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

unclebiguns said:

Not really surprised as the implicit conversion truncates the data.  What is interesting is that inserting the longer value works as well through truncating, but if you try the same thing with char or varchar you get string or binary data would be truncated error unless you do an explicit conversion.

May 27, 2008 3:11 PM
 

Denis Gobo said:

I would have expect something like this would happen

Create Table #Blah (Data VarChar(4))

Insert Into #Blah Values('Hell')

Select * from #Blah Where Data = 'Hello World'

This works too

SELECT CONVERT(uniqueidentifier,'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')

I guess it does this first behind the scenes and then convert to guid

SELECT CONVERT(varchar(36),'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')

May 27, 2008 3:18 PM
 

Hugo Kornelis said:

Hi Denis,

There's a vast difference between the examples in the post and in your comment. In the first query in the post, you compare uniqueidentifier to char(47); based on the rules of data type precedence (see BOL), char gets converted to uniqueidentifier because the latter has higher precedence. It's equally unsurprising that the string is truncated during the conversion, as this is clearly documented in BOL (in the article on CONVERT).

The query in your comment, though, compares varchar(4) to char(11). The rules of data type precedence force a conversion from char(11) to varchar(11). After that, comparison between two varchar values takes place. As defined in the ANSI standard (and probably in BOL as well, though I didn't care to bother), the shorter of the strings is padded to match the length of the longer, after which a byte by byte comparison takes place. So you are comparing 'Hell       ' (yes, there are seven spaces there - unless some HTML god mangles them) to 'Hello world'. There'll be a difference at position 5, and the values are thus unequal.

May 28, 2008 3:48 AM
 

Denis Gobo said:

Hugo, you are right, I couldn't think of a better example at that moment  :-(

What I tried to say (and it probably doesn't make a lot of sense) is that it would be nice if for both it would work the same so

WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'

would not return anything

May 28, 2008 10:01 AM
 

Alok said:

Hi alok here ,i hope it is not applicable for ado.net

  is the best of comming sequence 2005

June 6, 2008 1:17 PM
 

Jeff Fischer said:

Check out my blog article on SQL Server unique identifier solution that's both globally unique and globally sequential.  This handy script will save you hours and bring your SQL application to new performance levels at the same time!

October 13, 2009 3:07 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement