4

I have a table where the primary key is of type Guid. In my MVC application, I want to add a record to this table. I know of the Guid.newGuid() that creates a new Guid. This workds well but I have a concern. How does one ensures that the created Guid is unique (does not yet exist in the database)? Is there a way to generate it by comparing already existing values to make sure that the new guid is unique across the database records?

tereško
  • 58,060
  • 25
  • 98
  • 150
jpo
  • 3,959
  • 20
  • 59
  • 102
  • You can take reference at here: http://stackoverflow.com/questions/1752004/sequential-guid-generator-c-sharp Or at here: http://stackoverflow.com/questions/1155454/performance-value-of-comb-guids – Toan Vo Apr 24 '13 at 15:24

3 Answers3

4

The entire purpose of the guid generation technique is that it doesn't need to. The algorithm will generate a globally unique value even if it doesn't have access to all of the other previously generated GUIDs.

In particular, the algorithm is to just generate one big random number. There are so many bits of data in the GUID that the odds of two of them having the same value are infinitesimally small. Small enough that they truly can be ignored.

For a more detailed analysis see Eric Lippert's blog on the subject. (In particular part three.)

Note that, as a consequence of this, using a GUID as a unique identifier will take up quite a bit more space in the database then just using a numeric identifier. Any decent database will have a special column type specifically designed to be a unique identifier that it will populate; such a column will be able to ensure uniqueness while using quite a lot less space than a GUID.

Servy
  • 202,030
  • 26
  • 332
  • 449
  • 2
    Good point on the space issue. Note that if used as primary key, the fact that it is a large datatype will be magnified when indexes are used on that table because it stores that primary key for each record. – Scott Adams Apr 24 '13 at 15:27
  • 1
    @ScottAdams - nit picking time. At least in SQL Server, not true. What is true is that the *clustered index* key(s) is stored in each (non clustered) index. But the clustered index and primary key are orthogonal concepts. – Damien_The_Unbeliever Apr 29 '13 at 13:57
  • 1
    @Damien_The_Unbeliever, good point and an important distinction when they are not one in the same. So, to change, "Note that if used as your clustered index key, the fact that it is a large datatype will be magnified when non clustered indexes are used on that table because it will be stored as a look up for each non clustered index record. – Scott Adams Apr 29 '13 at 15:35
2

The possibility od generating a duplicate is very low. However you could enforce a UNIQUE constraint on the database table.

Darren
  • 68,902
  • 24
  • 138
  • 144
  • 1
    This should be downvoted, not upvoted. Suggesting a workaround for a problem that doesn't exist is a waste of our time. – Bent Tranberg Oct 26 '14 at 10:27
  • @BentTranberg - no it shouldn't. If you have nothing constructive to say then don't comment on mine and other people's answers when you haven't provided a more suitable answer yourself. – Darren Oct 26 '14 at 10:30
1

However there are very little chances that the new GUId will match with anyone present in database.

But if you still want to be sure just create a proc or something that will give true or false for a GUID. If it returns true then generate again and repeat this process till a unique GUID is not achieved.

Nikhil Agrawal
  • 47,018
  • 22
  • 121
  • 208
  • OP is creating the quid outside of the database – Scott Adams Apr 24 '13 at 15:24
  • its GUID and not quid. and i know that he is generating outside DB. – Nikhil Agrawal Apr 24 '13 at 15:26
  • So, you would create a quid/GUID on in your client code. Then you would on the save to the DB, call a proc that would cycle until a unique one was found. Not caring that it would already be unique anyway. And then finish the save? If OP wanted to create that mess, why wouldn't they just rely on the DB and use default value of NEWID()? – Scott Adams Apr 24 '13 at 15:38
  • This should be downvoted, not upvoted. Suggesting a workaround for a problem that doesn't exist is a waste of our time. – Bent Tranberg Oct 26 '14 at 10:26