SQL query does not work properly

Question

I am using the standard ASP.NET membership table structure in SQL Server and have performed several queries manually in the management studio and have run this query

SELECT * FROM [aspnet_Users] WHERE UserId = '2ac5dd56-2630-406a-9fb8-d4445bc781da&cID=49' 

Pay attention to & cID = 49 at the end - I copied this from the query string and forgot to delete this part.

However, to my surprise, he returned the data correctly (there is a user with ID 2ac5dd56-2630-406a-9fb8-d4445bc781da) - any idea why this works? In my opinion, this should not coincide or, most likely, with an error, since it will not be able to convert to Guid?

Answer 1

The uniqueidentifier type is considered a character type for conversion purposes from a character expression and therefore is subject to truncation rules for conversion to a character type. That is, when character expressions are converted to a character data type of different sizes, values ​​that are too large for the new data type are truncated .

Because the uniqueidentifier type is limited to 36 characters , characters that exceed this length are truncated.

_{Please note that the above is the MSDN}

Answer 2

The parser is (wonderful) lenient when converting string literals to guide literals, apparently:

 SELECT CAST('E63F4FFC-8574-428B-B6B8-95CFCA05ED52' AS uniqueidentifier) SELECT CAST('E63F4FFC-8574-428B-B6B8-95CFCA05ED52a' AS uniqueidentifier) SELECT CAST('E63F4FFC-8574-428B-B6B8-95CFCA05ED52-!' AS uniqueidentifier) SELECT CAST('E63F4FFC-8574-428B-B6B8-95CFCA05ED52~#5' AS uniqueidentifier) SELECT CAST('E63F4FFC-8574-428B-B6B8-95CFCA05ED52$3%] ' AS uniqueidentifier) 

all give the same result, no mistakes.

This is a documented behavior , so we cannot complain:

The following example shows data truncation when the value is too large for the data type being converted. Because the uniqueidentifier type is limited to 36 characters, characters that exceed this length.

 DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong'; SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue; 

Here is the result.

 String TruncatedValue -------------------------------------------- ------------------------------------ 0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong 0E984725-C51C-4BF4-9960-E1C80E27ABA0 (1 row(s) affected)