CODEWITHSUNDEEP
c#asp.netsqlvisual-studio-2010sql-server-2008
Pradeep
Pradeep

Reputation: 2729

Is there any better option for GUID creation than System.Guid.NewGuid() in .net

In my application code i am generating GUID using System.Guid.NewGuid() and saving this to SQL server DB.

I have few questions regarding the GUID generation:

  1. when I ran the program I did not find any problem with this in terms of performance, but I still wanted to know whether we have any other better way to generate GUID.
  2. System.Guid.NewGuid() is this the only way to create GUID in .NET code?

Upvotes: 0

Views: 3477

Answers (4)

user1429080
user1429080

Reputation: 9166

To answer the question:

Is there any better option for GUID creation than System.Guid.NewGuid() in .net

I would venture to say that System.Guid.NewGuid() is the preferred choice.

But for the follow up question:

...saving this to SQL server DB.

The answer is less clear. This has been discussed on the web for a long time. Just Google "guid as primary key" and you'll have hours of reading to do.

Usually when you use a Guid in Sql server it is for the reason of using as primary keys in tables. This has many nice advantages:

  • It's easy to generate new values without accessing the database
  • You can be reasonably sure that you locally generated Guid will NOT cause a primary key collision

But there are significant drawbacks as well:

  • If the primary key is also the clustered index, inserting large amounts of new rows will cause a lot of IO (disc operations) and index updates.
  • The Guid is quite large compared to the other popular alternative for a surrogate key, the int. Since all other indexes on the table contain the clustered index key, they will grow much faster if you have a Guid vs an int.
  • Which will also cause more IO since those indexes will require more memory

To mitigate the IO issue, Sql Server 2005 introduced a new NEWSEQUENTIALGUID() function which can be used to generate sequential Guids when inserting new rows. But if you are ging to use that, then you will have to be in contact with the database to generate one, so you lose the possibility to generate one when off line. In this situation you could still generate a normal Guid and use that.

There are also many articles on the web about how to roll your own sequential Guids. One sample:

http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

I have not tested any of them so I can't vouch for how good they are. I chose that specific sample because it contains some information that might be interesting. Specifically:

It gets even more complicated, because one eccentricity of Microsoft SQL Server is that it orders GUID values according to the least significant six bytes (i.e. the last six bytes of the Data4 block). So, if we want to create a sequential GUID for use with SQL Server, we have to put the sequential portion at the end. Most other database systems will want it at the beginning.

EDIT: Since the issue seems to be about inserting large amounts of data using bulk copy, a sequential Guid will probably be needed. If it's not necessary to know the Guid value before inserting then the answer by Jon Egerton would be one good way to solve the issue. If you need to know the Guid value beforehand you will either have to generate sequential Guids to use when inserting or use a workaround.

One possible workaround could be to change the table to use a seeded INT as primary key (and clustered index), and have the Guid value as a separate column with a unique index. When inserting the Guid will be supplied by you while the seeded int will be the clustered index. The rows will then be inserted sequntially, and your generated Guid can still be used as an alternative key for fetching records later. I have no idea if this is a feasible solution for you but it's at least one possible workaround.

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

NewGuid would be the generally recommended way - unless you need sequential values, in which case you can P/Invoke to the rpcrt function UuidCreateSequential:

Private Declare Function UuidCreateSequential Lib "rpcrt4.dll" (ByRef id As Guid) As Integer

(Sorry, nicked from VB, sure you can convert to C# or other .NET languages as required).

Upvotes: 0

usr
usr

Reputation: 171246

The GUIDs generated by Guid.NewGuid are not sequential according to SQL Servers sort order. This means you are inserting randomly into your indexes which is a disaster for performance. It might not matter, if the write volume is small enough.

You can use SQL Servers NEWSEQUENTIALGUID() function to create sequential ones, or just use an int.

Upvotes: 5

Jon Egerton
Jon Egerton

Reputation: 41589

One alternative way to generate guids (I presume as your PK) is to set the column in the table up like this:

create table MyTable(
    MyTableID uniqueidentifier not null default (newid()),
...

Implementing like this means that you've the choice whether or not to set them in .Net or to let SQL do it.

I wouldn't say either is going to be "better" or "quicker" though.

Upvotes: 2

Related Questions