CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
GibboK
GibboK

Reputation: 73908

Create a Unique GUID on a View with SQL

I need to create a View for a MS Sql table (which has NO Id), the new View must contain a UNIQUE Id. At the moment I'm using the ROW number and stuff function to Create an Id.

Unfortunately having a big table the result is some duplicated ID.

I believe the problme could be in '72799568-6EF2-4C95-84E7-4953A6959C90' any idea how I could solve it?

CREATE VIEW viewWithId as
SELECT convert(uniqueidentifier,
       stuff('72799568-6EF2-4C95-84E7-4953A6959C90',1,len(rn),convert(varchar,rn))) [Id],
       T.[EventId], 
       T.[EventTitle]
FROM 
    ( 
        select  x.[EventId], 
                x.[EventTitle]
                ROW_NUMBER() over (order by x.EventId) rn
        FROM    dbo.A as x 
    ) T

Upvotes: 1

Views: 5462

Answers (3)

D Stanley
D Stanley

Reputation: 152511

The reason you get duplicates is because rows 1, 12, and 127 (in general rows n, 10*n+2, and 100*n + 27) all return the same identifier. If you NEED a GUID then just use

CREATE VIEW viewWithId as
SELECT NEWID() [Id],
       T.[EventId], 
       T.[EventTitle]
FROM 
    ( 
        select  x.[EventId], 
                x.[EventTitle]
                ROW_NUMBER() over (order by x.EventId) rn
        FROM    dbo.A as x 
    ) T

The problem, however, is that you get a different UUID for each row when you re-select the view.

If you try to "generate" a GUID from the Id you'll have to come up with an algorithm that is guaranteed to be unique for any Id. Another option would be to start at 0 and add the Id to the end:

SELECT convert(uniqueidentifier,
           stuff('72799568-6EF2-4C95-84E7-000000000000',
                 36-LEN(rn),
                 len(rn),
                 convert(varchar,rn)
                )
            ) [Id],
       T.[EventId], 
       T.[EventTitle]
FROM 
    ( 
        select  x.[EventId], 
                x.[EventTitle]
                ROW_NUMBER() over (order by x.EventId) rn
        FROM    dbo.A as x 
    ) T

That will give you enough "unique" IDs for 999,999,999,999 (~1 Trillion) rows.

Upvotes: 3

Sebastian Meine
Sebastian Meine

Reputation: 11773

if you want the guid to be dependent on the eventid, use this code:

CREATE VIEW viewWithId as
SELECT convert(uniqueidentifier,
       '72799568-6EF2-4C95-'
        + STUFF(CONVERT(VARCHAR(36), CAST(rn AS VARBINARY(8)), 2),5, 0, '-')
       ) [Id],
       T.[EventId], 
       T.[EventTitle]
FROM 
    ( 
        select  x.[EventId], 
                x.[EventTitle],
                ROW_NUMBER() over (order by x.EventId) rn
        FROM    dbo.A as x 
    ) T

It incorporates the full HEX representation of the ROW_NUMBER into the guid.

If the EventId is unique and never changes for a row, new EventIds are always larger then all existing (e g IDENTITY value), you never delete a row and all inserts use table locks this will create the same value for every row every time. If any of the above is not given, your only way to solve this is to add a guid column to the table, unless you don't care if the values change between successive runs.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294207

You can use NEWID() to project a random new GUID. Obviously though projecting a random guid will not be the same as projecting a row identifier (a primary key). In fact, there is basically no way to project a correct primary key value if the table doesn't have a database enforced primary key. Any effort will be futile in presence of concurrency and updates (specially deletes).

Upvotes: 2

Related Questions