Requirements: During a recent interview, I was asked what is the difference between SQL Server database int and guid as the main key.Both have pros and cons, please weigh them according to your actual business scenarios。
Original text 1：The hyperlink login is visible.
Advantages of using INT as the primary key:
1. It requires a small data storage space, only 4 bytes.
2. The performance of using INT for insert and update operations is better than that of GUID, so using int will improve the performance of the application.
3. Index and Join operations, int has the best performance.
4. Easy to remember.
5. Support to obtain the latest value through a function, such as: Scope_Indentity().
Disadvantages of using INT as the primary key
1. If there is a frequent operation of merging tables, there may be duplication of primary keys.
2. There are restrictions on the scope of using INT data. If there is a large amount of data, it may exceed the INT value range.
3. It is difficult to process distributed stored data tables.
Advantages of using GUID as the primary key:
1. It is unique.
2. There are few opportunities for duplication.
3. It is suitable for insertion and update operations in a large amount of data.
4. Cross-server data merging is very convenient.
Disadvantages of using GUID as the primary key:
1. The storage space is large (16 byte), so it will take up more disk size.
2. It is difficult to remember. The performance of the join operation is lower than that of int.
3. There is no built-in function to obtain the latest generated GUID primary key.
4. The GUID as the primary key will be added to other indexes on the table, so it will reduce performance.
Summary:
The above lists the advantages and disadvantages of GUID and INT data types as primary keys. I think for large data volumes, it is recommended to use guid as the primary key. whereasUsing int will give you the best performance。
Comment: UseOrdered GUID performance improves a lot。
Original text two：The hyperlink login is visible.
identity is a whole type type, decimal without decimal definition, bigint is acceptable
The guid is fixed binary(16), which is 16 bytes
identity is incremented (or subtracted), and guid is random, so as data is inserted,The former will not produce any fragments, while the latter will, and because the latter is out of order, the order must be adjusted when inserted, so the former is generally better than the latter in terms of performance.
A large amount of information is usually divided into tables, so identity is still more suitable in many cases
As for the meaning of the primary key, this depends on how you consider it when designing, for the data processing itself, the primary key does not need to be meaningful, and if you require the primary key to contain business meaning, then the identity primary key is of course meaningless, but generally speaking, it is better to separate the business meaning and data processing, the business is always changing, if you want to bring the meaning to the main key of the table design, then the business changes, you will have to adjust the table structure more.
Original text three：The hyperlink login is visible.
1. In systems that often need to do data migration, it is recommended to use Guid. And adding non-clustered indexes to the corresponding foreign key fields, that is, fields used for join queries, is of great benefit to improve performance. The field of the where condition can also be added as appropriate for non-clustered indexes.
2. When using the Guid type as the primary key, the data type should be uniqueidentifier and be sure to remember itCancel the primary key's "Aggregate indexes”
3. For systems that do not need to be migrated, or small systems, it is still very convenient to use int as the primary key, and there is still a certain improvement in efficiency.
(End)
|
Posted on 6/6/2023 10:17:40 PM
|
|
|
Landlord|
Posted on 6/6/2023 10:42:46 PM
|
