Why does SQL Server give me a bad output?


Accidently I noticed a bug-like behaviour in SSMS. I was querying from a table named Candidate with the below query.

 select CandidateId, CandidateName from Candidate
 where CandidateId='73415005-77C6-4D4B-9947-02D6B148E03F2'

I was copy-pasting the CandidateId which is a unique identifier, but somehow I added a two (2) in the end. Actually the candidate id I was querying to was '73415005-77C6-4D4B-9947-02D6B148E03F' and there is no candidate with candidateid 73415005-77C6-4D4B-9947-02D6B148E03F2 (that is not even a GUID i suppose)

But still, I was getting the result back.

You can see in the query and the result, the CandidateId's are different. Why is it happening so? Anyone please explain.

The top-level description is that the string is being converted to a unique identifier, so the last digit is ignored.

This logic is documented. First, unique identifiers have a slightly higher operator precedence than strings. The relevant part of the documentation:

  1. uniqueidentifier
  2. nvarchar (including nvarchar(max) )
  3. nchar
  4. varchar (including varchar(max) )
  5. char

This is why the conversion is to uniqueidentifier rather than to a string.

Second, this is a case where SQL Server does "silent conversion". That is, it converts the first 36 characters and doesn't generate an error for longer strings. This is also documented:

The following example demonstrates the truncation of data when the value is too long for the data type being converted to. Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length are truncated.

So, the behavior that you see is not a bug. It is documented behavior, combining two different aspects of documented SQL Server functionality.