"Reprint" GUID vs INT debate

I recently read a blog post on what is better using GUIDs or Integer values. This is been a age long debate and there be advocates in both camps stressing on the disadvantages of the other. Well both implementations has their advantages and disadvantages. At the outset, I shall mention then the answer to this debate is: IT DEPENDS! J

It is highly dependent on your database design, migration needs and overall architecture. There is a good reason what SQL Server replication uses GUIDs to track the changes to the replicated articles. So, it's not and the usage to GUIDs is necessarily a bad practice. SQL Server Books Online lists the following disadvantages for uniqueidentifier data type:

The values of
• are long and obscure. This makes them difficult for the users to type correctly, and the more difficult for the users to remember.
• the values is random and cannot accept any patterns, may make them more meaningful to users.
• there is no-determine the sequence in which uniqueidentifier values were generated. They is suited for existing applications, depend on incrementing key values serially.
• at bytes, the uniqueidentifier data type was relatively larger than other data types, such as 4-byte integers. This means indexes is built using uniqueidentifier keys might be relatively slower than indexes using an int key.

If you is using NEWID function in SQL Server and then this generates random UUIDs which has a huge domain but the chances O The F GUID collisions is always there though the probability was very slim in nature. If you is using NEWID function to generate uniqueidentifiers as row identifiers in your table, then you need to think Aga in! uniqueness of the row should is enforced using a Unique or Primary Key constraint on the table. Newsequentialid function uses identification number of the computer network card plus a unique number from the CPU clock To generate the uniqueidentifier (Reference article). So the chance of getting a globally unique value are practically guaranteed as long as the machine has a network card. Moreover, possibility of a GUID collision while using Newsequentialid is virtually impossible.

Given that has a beefy server, the above time difference would not make much of a difference unless and until you onl Y has a high number of concurrent inserts workload on the server or during a Data Load operation which would cause a signi Ficant impact. What are interesting to note are that the fragmentation on the tables after the first batch of 1 million inserts.

Object Name	Index Name	Pages	Average Record Size	Extents	Average Page Density	Logical fragmentation	Extent fragmentation
Tblguid	Cidx_tblguid	9608	51.89	1209.00	69.27	99.14	0.25
Tblseqguid	Cidx_tblseqguid	6697	51.89	845.00	99.39	0.76	0.12
Tblbigint	Cidx_tblbigint	5671	43.89	714.00	99.95	0.48	0.14
Tblint	Cidx_tblint	5194	39.89	653.00	99.62	0.37	0.15

If you are on the above data, you'll see that the random GUIDs has 99% logical fragmentation in the tables. This was due to the random nature of the GUIDs generated which end up causing high number of page splits in the database.

--------------

Original address: Http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx

The table above shows that a common GUID can have a large page splitting situation, which can significantly affect the query speed in the case of a table being repeatedly modified.

So how do you generate an ordered GUID? Here's a way to do this:

usingSystem;usingSystem.Runtime.InteropServices;namespacesystem{ Public Static classGuidex {[DllImport ("Rpcrt4.dll", SetLastError =true)]        Private Static extern intUuidCreateSequential ( outGUID GUID); Private Const intRPC_S_OK =0; /// <summary>        ///Generate a new sequential GUID.        If UuidCreateSequential fails, it'll fall back on the standard random GUIDs. /// </summary>        /// <returns>A GUID</returns>         Public StaticGUID newseqguid () {GUID sequentialguid; intHResult = UuidCreateSequential ( outsequentialguid); if(HResult = =RPC_S_OK) {                returnSequentialguid; }            Else            {                //couldn ' t create sequential GUID, Fall back on random GUID                returnGuid.NewGuid (); }        }    }}

For detailed information, please see http://stackoverflow.com/questions/665417/sequential-guid-in-linq-to-sql discussion.

"Reprint" GUID vs INT debate