Like # intepreted in Guid / UNIQUEIDENTIFIER (SQL Server)

Question

Here is the behavior that I just found out by mistake. There is a UNIQUEIDENTIFIER column in the table on SQL Server, and I ran a query like:

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

The # 1 at the end of Guid got there by mistake, as I copied it from a URL adding # 1, # 2, # 3, etc., representing the paging.

What surprised me was that the request went fine and I got the same result as when I started:

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

Does anyone know how # and anything after it has been indexed in such a scenario?

Answer 1

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

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

Explanation

After John Gatoy’s comment on the case of '{GUID}[gibberish]' (and after adoption), I think I can expand the rule a bit.

1) If this line starts with '{' , then the 38th MUST be '}' (try even leading and trailing spaces inside - it will not work), otherwise the conversion will fail. Then 36 characters are converted inside.

2) Otherwise, the first 36 characters are used.

So, you can add :) , << and antidisestablishmentarianism - after the 38th character in 1) or the 36th character in 2), it does not matter.

Answer 2

GUID is a fixed width, so the extra character is deleted during type conversion;

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

Answer 3

I suspect that the query engine sees that it is a unique identifier and is internally just truncated with 36 characters, so everything else is simply ignored. This also works great, so it has absolutely nothing to do with the # sign:

 SELECT CONVERT(UNIQUEIDENTIFIER, 'F9B8E808-E589-499B-8E57-22B7CBB2D63E ... and here is some extra garbage for fun'); 

Results:

 ------------------------------------ F9B8E808-E589-499B-8E57-22B7CBB2D63E