When using Guid.NewGuid() to create GUIDs for indexed columns in SQL Server beware of potential performance issues on large tables.
GUIDs generated with this method are not ever-increasing so will be inserted at random locations in relevant indexes not at the end which is better for write performance as the whole index doesn’t have to shuffle upon every insert.
If you can’t switch to int or bigint, sequential GUIDs can be used to mitigate potential performance issues. Entity Framework Core has a SequentialGuidValueGenerator class which allows us to produce sequential GUIDs which are sortable with respect to SQL Server. If you don’t use Entity Framework you can still use the class… just pull Microsoft.EntityFrameworkCore down from NuGet and reference the class…
The image below shows 10 non-sequential and 10 sequential (with respect to SQL Server) GUIDs being inserted and selected back from two different tables…
Note 1 –
SQL Server creates clustered indexes for primary keys by default.
Note 2 –
Sequential GUIDs are guessable so avoid in security sensitive contexts.
