ASCII values for GUID

Question

ASCII values for GUID

Nandy

Ten Centuries

Points: 1016
More actions
February 20, 2009 at 2:18 am

#131086

Hi All,
Please can anyone suggest me in this regard. I have numeric field in the table and due to the recent changes in the project, I will get GUID value where I need to insert in the numeric field. Initially planned to change the table and add new field for the GUID value, however if I make change to my current table, entire business object need to be changed. To avoid this, I have converted the GUID values to ASC(GUID) from ASP page and passed the value to the table, which is working fine. However when ever I check ASCII(GUID) in SQL Server am getting same values for different GUID's.
My question is why ascii(GUID) values are same for different GUID's in SQL Server 2000. Is there any way where I can avoid this and GUID values are unique.
Is there any other way where I can convert the GUID value in ASP and pass to the SQL Server to insert in the table and by using the same value where I can get my GUID back.
Below are ascii values for different GUID's in SQL server.
48 03b3f5df-68e4-4811-893e-3b1031e93a5d
48 065c7347-3f25-4dbf-ac4c-330ce670a0a2
48 0a4bc266-2c2c-4003-92d6-44aa147010bb
48 0ab75305-41e7-4cc7-bf0a-eaa5b4f3a32f
48 0b54def1-dd7b-491d-bead-a154cffd748a
48 0bb61311-698e-4a3c-9281-ffce69033252
49 11ac12d7-fc31-4ebb-b05d-66c017ce833c
49 134a5cc8-1a4c-4649-ad91-452cc6326866
49 18a8e995-aa78-4486-94ab-b88f936e3b03

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

My question is why ascii(GUID) values are same for different GUID's in SQL Server 2000. Is there any way where I can avoid this and GUID values are unique.

The ASCII function is returning the character code of the first hexadecimal digit of the GUID when converted to a hexadecimal string. There will only be 16 possible values returned by the ASCII function used in this way so it is not surprising that you have duplicates.

I'm afraid that if you are now being sent GUIDs, you don't have a choice but to change your table schema. Use the uniqueidentifier data type to store GUIDs.

Nandy Ten Centuries Points: 1016 More actions · Answer 2

Thanks for the suggestion.

However I have tried differently to send the GUID values to the numeric field in the table, instead of adding the new field in the table. I just used the CheckSum for the GUID in the SQL Server 2000, which will give the unique numeric values for GUID's. Some time we might get the negative Check Sum values for the guid, so I have used ABS(CheckSum(newid())), where am getting correct values.

I tried this and working fine.

Please if you any suggestion on the same, please let me know.

Cheers,

Nandy

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

You didn't try it enough. You will get dupes if you use CheckSum on GUID's.

SELECT TOP 1000000

CHECKSUM(NEWID()) AS CsNewID,

CAST(CAST(NEWID() AS VARBINARY) AS INT) AS IntNewID,

NEWID() AS MyNewID,

CAST(CAST(NEWID() AS VARBINARY) AS BIGINT) AS BigIntNewID

INTO #MyHead

FROM Master.dbo.SysObjects t1,

Master.dbo.SysObjects t2

SELECT CsNewID, COUNT(*)

FROM #MyHead

GROUP BY CsNewID

HAVING COUNT(*) > 1

SELECT IntNewID, COUNT(*)

FROM #MyHead

GROUP BY IntNewID

HAVING COUNT(*) > 1

SELECT MyNewID, COUNT(*)

FROM #MyHead

GROUP BY MyNewID

HAVING COUNT(*) > 1

SELECT BigIntNewID, COUNT(*)

FROM #MyHead

GROUP BY BigIntNewID

HAVING COUNT(*) > 1

{edit} And, my bad,[font="Arial Black"] Andrew is correct about even BIGINT not getting it right below[/font].

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

Is there any other way where I can convert the GUID value in ASP and pass to the SQL Server to insert in the table and by using the same value where I can get my GUID back.

A GUID is a 16 byte data type. The largest integer data type is bigint which has 8 bytes. Therefore, I can't see any way by which you can get your complete GUID back after having converted it to a bigint. When converting from a GUID to a bigint, there is still the possibility of duplicate numbers being generated from different GUIDs, although the likelihood is small.

You can recover half of the orignal GUID from a bigint. It's your choice whether it's the first half or the second half.

DECLARE @g0 uniqueidentifier

DECLARE @g1 uniqueidentifier

DECLARE @b-2 bigint

/* Store second half of GUID */

SELECT @g0 = NEWID()

SELECT @b-2 = CAST(CAST(@g0 AS BINARY(16)) AS bigint)

SELECT @g1 = CAST(CAST(@b AS BINARY(16)) AS uniqueidentifier)

SELECT [Original GUID] = @g0, [Number] = @b-2, [Recovered GUID] = @g1

/* Store first half of GUID */

SELECT @b-2 = CAST(CAST(@g0 AS BINARY(8)) AS bigint)

SELECT @g1 = CAST(CAST(@b AS BINARY(8)) AS uniqueidentifier)

SELECT [Original GUID] = @g0, [Number] = @b-2, [Recovered GUID] = @g1

A numeric data type with maximum precision (29-38) uses 17 bytes of storage, so it is potentially capable of storing the full 128 bits of a GUID. However, I have not been able to successfully cast a uniqueidentifier to a numeric(38) data type (via a varbinary data type), and get one of the following 2 errors:

[font="Courier New"]Error converting data type varbinary to numeric.

Arithmetic overflow error converting varbinary to data type numeric.[/font]

You can't fit a quart into a pint pot.