17
declare @uu uniqueidentifier =  'C50B0567-F8CC-4219-A1E1-91C97BD9AE1B'
select @uu
declare @zaza bigint = ( select convert(bigint, convert (varbinary(8), @uu, 1)) )
select @zaza
select CONVERT( uniqueidentifier , convert( varbinary(16) , @zaza , 1 ) )

I thought I had a fast way to convert Unique Identifier values to a Big Int, and back. But there is a problem in my second convert. Can anyone comment on the right way to fully convert a GUID to a number and back? I am only getting part of the GUID and not the whole thing when I try to convert it back from the numeric representation to its original GUID.

I want to pass an integer (I think it would be classified as a "Large BigInt" in MSSQL?) to a remote system and just use characters 0-9, and still get the random uniqueness of NewId().

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Snowy
  • 5,942
  • 19
  • 65
  • 119
  • 11
    `uniqueidentifier` is 16 bytes. A `bigint` is only 8. This isn't going to work. Why are you trying to do this? – Martin Smith Apr 09 '12 at 18:59
  • 4
    What would such a conversion mean??? – Oded Apr 09 '12 at 18:59
  • I edited the original question again to address this, I need to exchange data with a system that takes numbers but not alphanumeric codes and thought that a GUID that did not look like a GUID would work. – Snowy Apr 10 '12 at 14:18
  • I had this case just turn up for me, with a twist. I need to convert a four byte integer to a GUID and back again. Truncation is a non-issue as it's not starting as a GUID. – Joshua May 10 '12 at 22:21

1 Answers1

18

There is no problem with your second convert. When I run your SQL statement in SQL management studio, I get:

------------------------------------
C50B0567-F8CC-4219-A1E1-91C97BD9AE1B

(1 row(s) affected)


--------------------
7423352504965404994

(1 row(s) affected)


------------------------------------
C50B0567-F8CC-4219-0000-000000000000

(1 row(s) affected)

Since you are converting 8 byte value to 16-byte guid, half of guid will be zeroes, which is exactly what you are seeing.

galets
  • 17,802
  • 19
  • 72
  • 101