How is a # intepreted in a Guid/UNIQUEIDENTIFIER (SQL Server)

Question

Here is a behavior I only came to learn of by mistake. A table in SQL Server has a UNIQUEIDENTIFIER column and I ran a query like:

SELECT * FROM Tbl WHERE GuidColumn = N'2B375CD8-D210-463F-A2FD-EAFB0D643664#1'

The #1 at the end of the Guid got there by mistake as I had copy-pasted it from a url that was appending #1, #2, #3, and so on representing paging.

What surprised me is that the query ran just fine and I got the same result as I would get by running:

SELECT * FROM Tbl WHERE GuidColumn = N'2B375CD8-D210-463F-A2FD-EAFB0D643664'

Would anyone know how the # and anything after is intepreted in such a scenario?

Answer 1

This is dealt with explicitly on MSDN: http://msdn.microsoft.com/en-us/library/ms187942.aspx

It doesn't mean anything - SQL server only reads the first 36 characters of the string when converting to Guid.

Clarification

Following John Gathogo's comment about the '{GUID}[gibberish]' case (and after acceptance), I think I can expand the rule slightly.

1) If that string starts with '{', then the 38th MUST be '}' (try even leading and trailing spaces within - it won't work), otherwise conversion fails. Then the 36 characters within are converted.

2) Otherwise, the first 36 characters are used.

So you can add :), << and antidisestablishmentarianism - after the 38th character in 1) or the 36th in 2), it makes no difference.

Answer 2

A GUID is fixed width so the extra character is stripped during the type conversion;

declare @g uniqueidentifier = '2B375CD8-D210-463F-A2FD-EAFB0D643664#1'
select @g
>> 2B375CD8-D210-463F-A2FD-EAFB0D643664