Hi Jalpesh,

Really Good summary.

You can add one more thing in disadvantage of GUID - If you use newid() as function to generate the GUID and you have primary key, it will increase the fregmentation and slows insert and select both.

-Chintak.

Your conclusion is incorrect. The more data you have, the worse the decision to use a GUID is. 99.99999999% of the time, the int or bigint will be better option.

You didn't even discuss one of the biggest problems with GUIDs which is fragmentation. Also, not only are indexes bigger, they are slower.

But, you don't have trust me. Read what Kimberly Tripp says on the subject: http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx

One should define "large".

int columns can hold 2^32 unique values (-2^31 through 2^31) or about 4 billion values. Since most people don't bother with negative integers, that cuts the number of unique values in half to about 2 billion values. int uses 4 bytes of storage.

bigint makes a good compromise between using int or GUIDs, if your only consideration is the number of unique values available. bigint has the benefit of using 8 bytes instead of GUID's 16 bytes. bigint columns can hold -2^63 through 2^63 (-9,223,372,036,854,775,808) to (9,223,372,036,854,775,807).

If you have a single table with more than 9,223,372,036,854,775,807 rows, please contact me, I'd love to understand the use-case.

Chintak makes a good point: NEWID() can lead to fragmentation when used with a clustered index. Two suggestions to consider: - use a non-clustered index or -use the NEWSEQUENTIALID() function instead of NEWID(). NEWSEQUENTIALID() can only be used in DEFAULT expressions.

@Mark Thanks for your response.

@Robert- I am also saying same don't use GUID unless it is very much required to have

Actually, I once had someone explain to me that the fact that GUID's "fragment" came in handy for a certain use-case. If I remember correctly, it was most notably useful because it avoided 'hot-spots' in the table where a lot of updates were happening on "the more recent rows". I guess that having the data 'all over the place' instead of grouped at the end of the table made for less 'overlap' in the locking-department and hence a better throughput of the system. (many-many small & frequent updates). Otherwise I agree that int's are a lot easier & leaner to work with.

PS: to complete the list one could add rowversion (timestamp) as another alternative to get unique values (within the DB!!), although I'll immediately add that it's even a bigger pain to work with than GUID's =)

Roby, that was back in SQL 2000 and before. Since SQL 2005, SQL is designed to take advantage of hotspots. Hotspots are good now because it increases teh chances that you are working in memory instead of reading from disk.

Jalpesh, if you think we agree, then you typed your conclusion incorrectly. Your conclusion recommends using GUIDs for large sets of data. I am recommended the opposite viewpoint