Requirements: When using GUIDs as primary key aggregation indexes, it is recommended to use ordered GUIDs, because unordered GUIDs will cause waste of space and the resulting decrease in read and write efficiency.
Review:
Database GUID value
Globally Unique Identifier (GUID) data types in SQL Server are represented by data typesuniqueidentifier, which stores a 16-byte binary value. A GUID is a binary number whose main purpose is as an identifier that must be unique in a network with many computers on many sites.
Both Guid and SqlGuid have ways to compare different GUID values. The SqlGuid implementation uses SQL Server behavior,The last six bytes of the value are the most important。
Sequential GUIDs are inherently guessable, so don't use them in security-sensitive contexts。
Reference:The hyperlink login is visible.
The database is ordered by the GUID
SQL Server database has oneNewSequentialId()function to create an ordered GUID. When creating a table, you can set it as the default value of a GUID type field, and automatically create the value of the primary key when inserting new data (this function can only be used as the default value of the field, not directly called in SQL).
Example:
The NewSequentialId() function can only be used in databases, although Microsoft's MSDN documentation states that NEWSEQUENTIALID is a wrapper for the Windows UuidCreateSequential function.
Tip: Don't use the function if it's a confidentiality issue. BecauseIt is possible to guess the value of the next generated GUIDto access the data associated with that GUID.
Reference:The hyperlink login is visible.
The hyperlink login is visible.
.NET creates an ordered GUID
The UuidCreateSequential function depends on the compute hardware of the methodThe last 12 bits are actually the MAC address of the network card。
The code is as follows:
The results are as follows:
Shortcoming:
- This method requires DllImport to call the Windows library, so it is not cross-platform.
- It cannot be used in a clustered environment, where multiple machines write to the same database because the resulting GUIDs will be different from each other (function-dependent computing hardware), resulting in index fragmentation.
- If your Windows server restarts, the GUID may start at a lower range, resulting in index fragmentation.
Reference:
The hyperlink login is visible.
The hyperlink login is visible.
If you are using a .NET program to create a SQL SERVER ordered GUID, it is recommended to use a third-party class library:RT. Comb, the nuget command is as follows:
The code is as follows:
Output:
Reference:The hyperlink login is visible.
Ordered and unordered GUID primary key index fragments
Start by creating two tables with the following script:
The test inserts 100,000 pieces of data, and the code is as follows:
The test results are as follows:
| GUID | Time Consumed (ms) | Scan Density [Best Count: Actual Count], the higher the value, the better | Logical Scan Fragmentation, the lower the value, the better | Average page density (full) (Avg. Page density (full), the higher the value, the better | | Unordered GUID | 13367 | 12.58% [61:485] | 98.97% | 63.66% | | Order GUID | 14139 | 100.00% [39:39] | 0.32% | 99.61% |
The test inserts 1 million pieces of data again, with an out-of-order GUID taking 135203 ms and an ordined GUID taking 135134 ms. The storage space occupancy is as follows:
Unordered GUID: 36.547 MB
Ordered GUID: 26.609 MB
Reference:The hyperlink login is visible.
(End)
|
Posted on 6/8/2023 9:12:26 PM
|
|
|
|
Landlord|
Posted on 1/27/2024 11:07:12 AM
|
