Generating GUID

Question

I am thinking to use a GUID in my .net app which uses SQL Server. Should I be writing a stored procedure which generates the GUID on each record entered or should I be directly generating it from the application.

Reasons for asking the question (If am wrong correct me in this):

I (as/pre)sume:

When generating the GUID from the database, you can assume that the DB remembers the previous generated GUID where as the application remembering it is difficult.

Answer 1

No, your assumption is wrong: the database won't be remembering anything - so there's no benefit from that point of view.

If you're using the GUID as your primary key / clustering key in SQL Server, which is a bad idea to begin with (see here, here or here why that's the case), you should at least use the newsequentialid() function as default constraint on that column.

CREATE TABLE YourTable(ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID()) 

That way, the database would generate pseudo-sequential GUID's for your PK and thus would make the negative effects of using a GUID as PK/CK at least bearable....

If you're not using the GUID as your primary key, then I don't see any benefit in creating that GUID on the server, really.

Answer 2

SQL Server has the creation of GUID's built in. There is no need to write a separate stored procedure for this.

You can use

• NEWID()
• NEWSEQUENTIALID()

The key difference between both procedures would be that the sequential GUID should be used if it is for a primary clustered key.

I'm not sure why you would want the database engine to remember the previous generated GUID.

Answer 3

My preference is to create GUID in the application not the db.

• Simplifies the retrieval of rows after insertion.
• Easier domain/business layer unit testing.
• Faster. At least for entity framework. http://blogs.msdn.com/b/adonet/archive/2010/06/28/performance-impact-of-server-side-generated-guids-in-ef.aspx