CODEWITHSUNDEEP
sqlsql-serverssms
snippetkid
snippetkid

Reputation: 293

Why is SQL Server giving me wrong 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.

enter image description here

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

Upvotes: 1

Views: 342

Answers (4)

Muhammet Yasin ARLI
Muhammet Yasin ARLI

Reputation: 564

SQL Truncate data when the value is too long for the data type being converted to. Since you try to compare uniqueidentifier field with text variable, SQL convert it to uniqueidentifier. It is not a bug.

Ex: select Cast('73415005-77C6-4D4B-9947-02D6B148E03F2' as uniqueidentifier)

Result : 

73415005-77C6-4D4B-9947-02D6B148E03F

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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.

Upvotes: 5

Tom V
Tom V

Reputation: 1496

That's because you probably have a convert_implicit in your execution plan and SQL converted '73415005-77C6-4D4B-9947-02D6B148E03F2' into a guid.

enter image description here

Upvotes: 5

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

Because your column CandidateId is of type GUID the right (string) part of the condition gets converted to uniqueidentifier data type and truncated. You can see this in your execution plan. There will be a Scalar Operator(CONVERT_IMPLICIT(uniqueidentifier,[@1],0)) in your index seek/scan operator.

Upvotes: 5

Related Questions