Convert from Uniqueidentifier to Bigint and Back

Convert from UniqueIdentifier to BigInt and Back?

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.

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

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

Conversion from 'uniqueidentifier' to 'int' is not supported on the connected database server

You have to add a new column ( ALTER TABLE ADD [NewId] INTEGER ) then run the following to populate the new id column :

WITH Cte
AS
(
    SELECT *
    , ROW_NUMBER() OVER(ORDER BY [Your GUID Column Here] DESC) AS RowNumber
    FROM YourTable
)
UPDATE Cte
SET [NewId]= RowNumber
GO

There you have a new ID column that you can use a clustered primary key

Change datatype of column to uniqueidentifier from bigint

You cannot convert from an integer to a uniqueidentifier. But you can do it like this.

1. First delete old data from the table.

2. Alter the column to some text-format (such as VARCHAR(200)).

   ALTER TABLE dbo.tbltest  
   ALTER COLUMN ID VARCHAR(200)
   

3. Now again
   
   

   ALTER TABLE dbo.tbltest  
   ALTER COLUMN ID uniqueidentifier
   

To be clear, you can't convert a column from numeric to uniqueidentifier directly, but you can convert numeric to varchar to uniqueidentifier.

Convert a Guid string into BigInteger and vice versa

Please check this:

public static Guid ToGuid(BigInteger value)
{
     byte[] bytes = new byte[16];
     value.ToByteArray().CopyTo(bytes, 0);
     return new Guid(bytes);
}

Edit: Working Fiddle

Approach for altering Primary Key from GUID to BigInt in SQL Server related tables

It certainly sounds like this strategy would work -- dropping the constraints, changing the column out from underneath them (type changes, name remains the same), and then recreating the constraints is fairly elegant.

Is the goal to ultimately drop the GUID columns? If so, you won't actually reclaim the space unless the tables are copied or rebuilt, so maybe the following adjustment:

...

4.Script the data change "update table x, set NewPrimaryKey = y where OldPrimaryKey = z

5.Drop the original primarykey to 'oldprimarykey'

6.Rename the 'NewPrimaryKey' column 'PrimaryKey'

7.Script back all the indexes and fkeys (building clustered indexes "rebuilds" tables)

8.For all tables that don't have clustered indexes, do something to make sure they get rebuilt and their space is reclaimed (such build and then drop a clustered index)

Needless to say, test it on a dev box before running on Production!

Convert uniqueidentifier type to string

That is the NewID() GUID
As unique as it gets in your DB

If you wanted a more readable Id you should have used INT/BigInt as a primary key with Identity(1,1) as your UserID

All of these return exactly the same thing

DECLARE @User_ID UNIQUEIDENTIFIER = NEWID()
SELECT CAST(@User_ID AS NVARCHAR(50))
SELECT CONVERT(NVARCHAR(50),@User_ID)
SELECT @User_ID

unique_identifier is exactly that, a unique identifier, not some kind of encrypted value of a common string or int

Is it better to use an uniqueidentifier(GUID) or a bigint for an identity column?

That depends on what you're doing:

• If speed is the primary concern then a plain old int is probably big enough.
• If you really will have more than 2 billion (with a B ;) ) records, then use bigint or a sequential guid.
• If you need to be able to easily synchronize with records created remotely, then Guid is really great.

Update
Some additional (less-obvious) notes on Guids:

• They can be hard on indexes, and that cuts to the core of database performance
• You can use sequential guids to get back some of the indexing performance, but give up some of the randomness used in point two.
• Guids can be hard to debug by hand (where id='xxx-xxx-xxxxx'), but you get some of that back via sequential guids as well (where id='xxx-xxx' + '123').
• For the same reason, Guids can make ID-based security attacks more difficult- but not impossible. (You can't just type 'http://example.com?userid=xxxx' and expect to get a result for someone else's account).

uniqueidentifier is incompatible with bigint entity framework c# code first

With three steps i changed it :
first
I added another column to my entity

public GUID OrganizationId { set; get; }
public int64 OrganizationId2 { set; get; }

second
I removed the guid column in my entity

public int64 OrganizationId2 { set; get; }

last step
finally i renamed my new column

public int64 OrganizationId { set; get; }

sql server change PK type from int to uniqueidentifier

You'll have to do it the hard way, using scripts:

0) get in single user mode

1) add the new GUID column to the main table, and populate it.

2) add the new FK column to each child table and populate them with an UPDATE FROM

UPDATE c
    SET FKcolumn=p.NewGuid
    FROM ChildTable             c
        INNER JOIN ParentTable  p ON p.OldIntID=c.OldIntId

3) drop the existing int FKs

4) drop the old int columns

5) add the new FKs on the guid column

6) get out of single user mode

you should be able to get SQL Server Management studio to generate the scripts for adding and dropping the columns and keys. Just make the changes in SSMS and click on the "Generate Change Script" toolbar icon and you can cut and paste the code to a text file.

---

Related Topics