SQL Server 2008 Convert from BIGINT to UNIQUEIDENTIFIER/GUID and Back

Question

I am trying to solve a problem very similar to Convert from UniqueIdentifier to BigInt and Back? Unfortunately that example doesn't show the whole process, it only shows converting a GUID back to BIGINT.

I'll call this a hypothetical problem since I've already decided that storing integers as GUID's isn't very beneficial. However at this point, I simply want to know why the conversion process isn't working.

Everything looks good until step 5....

SELECT  1 AS Step
        ,100966116980299

SELECT  2 AS Step
        ,CONVERT(VARBINARY(8), 100966116980299, 1)

SELECT  3 AS Step
        ,CONVERT(UNIQUEIDENTIFIER, 
            CONVERT(VARBINARY(8), 100966116980299, 1)
        )

SELECT  4 AS Step   
        ,CONVERT(VARBINARY(8), 
            CONVERT(UNIQUEIDENTIFIER, 
                CONVERT(VARBINARY(8), 100966116980299, 1)
            ), 1
        )

SELECT  5 AS Step   
        ,CONVERT(BIGINT, 
            CONVERT(VARBINARY(8), 
                CONVERT(UNIQUEIDENTIFIER, 
                    CONVERT(VARBINARY(8), 100966116980299, 1)
                ), 1
            )
        )

Results

Step    Value
1       100966116980299
2       0x0F0000014B768901
3       0100000F-764B-0189-0000-000000000000
4       0x0F0000014B768901
5       1080863916129945857

Lamak · Accepted Answer · 2012-08-07T03:30:38.850

I think that the problem here is that the value 100966116980299 that you are using is not being interpreted as BIGINT in the first place. Take a look to what happens if you first do an explicit cast to BIGINT:

SELECT  1 AS Step
        ,CAST(100966116980299 AS BIGINT)

SELECT  2 AS Step
        ,CONVERT(VARBINARY(8), CAST(100966116980299 AS BIGINT), 1)

SELECT  3 AS Step
        ,CONVERT(UNIQUEIDENTIFIER, 
            CONVERT(VARBINARY(8), CAST(100966116980299 AS BIGINT), 1)
        )

SELECT  4 AS Step   
        ,CONVERT(VARBINARY(8), 
            CONVERT(UNIQUEIDENTIFIER, 
                CONVERT(VARBINARY(8), CAST(100966116980299 AS BIGINT), 1)
            ), 1
        )

SELECT  5 AS Step   
        ,CONVERT(BIGINT, 
            CONVERT(VARBINARY(8), 
                CONVERT(UNIQUEIDENTIFIER, 
                    CONVERT(VARBINARY(8), CAST(100966116980299 AS BIGINT), 1)
                ), 1
            )
        )

Results

Step    Value
1       100966116980299
2       0x00005BD40189764B
3       D45B0000-8901-4B76-0000-000000000000
4       0x00005BD40189764B
5       100966116980299

Nice catch. Of all the variations I tried, I didn't think to CAST the number itself — Tom Halladay, Aug 03 '12 at 20:37

SQL Server 2008 Convert from BIGINT to UNIQUEIDENTIFIER/GUID and Back

1 Answers1