ASCII values for GUID

  • 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

  • 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.

  • 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

  • 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)

  • 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.

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

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