Does auto increment on Primary Key prevent concurent inserts? I have a Sql Server logging table, that continuously is being inserted into, I want to revise the design of this table.

Currently, we are using a GUID as the PK,  because the software can generate this and we just insert it, and hopefully, multiple inserts won't block each other. However, UniqueId is not a very efficient PK for a table and our indexes are very large and fragmented.

If I change this column to a long with auto-increment enabled.  Will this become a concurrent insert bottleneck?

I do not need my primary key to be in any sort of order, Just a unique id for that row?
If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck?


The answer is NO because the step when SQL Server gets next identity value is outside of transaction. This is why when you perform ROLLBACK identity does not decrease and you can have gaps.

Please see answer to similar question here