Advantages and disadvantages of SQL GUID and auto-increment key

Our company's database is all using the GUID key, many people are accustomed to use the INT key. So, here's a summary of the two data types of the master key for a comparison.

Advantages of using the INT key:

1, need very small data storage space, only need 4 byte.

2, insert and UPDATE operations use INT for better performance than GUIDs, so using int will improve the performance of your application.

3, index and join operations, INT is the best performance.

4, easy to remember.

5, support through the function to get the latest value, such as: Scope_indentity ().

Disadvantages of using int key

1, if the operation of the table is often merged, the primary key duplication may occur.

2, the use of INT data range is limited. If there is a large amount of data, the range of values for int may be exceeded.

3, it is difficult to deal with distributed storage data tables.

Advantages of using the GUID master key:

1, it is unique.

2, there is less chance of duplication.

3, suitable for large amount of data in the insert and update operations.

4, cross-server data merging is very convenient.

Disadvantages of using the GUID master key:

1, storage space is large (in bytes), so it will take up more disk size.

2, difficult to remember. Join operation performance is lower than Int.

3. There is no built-in function to get the newly generated GUID primary key.

4. The GUID master key will be added to the other indexes on the table, thus reducing performance.

Summarize:

The advantages and disadvantages of the GUID and int two data types are listed above. I think it's recommended to use the GUID master key for large data volumes. Using int will give you the best performance.

Transferred from: http://www.cnblogs.com/zhuqil/archive/2010/12/02/1894575.html

Advantages and disadvantages of SQL GUID and auto-increment key