Using newsequentialID for GUID columns in Azure SQL DB

mo boy 396 Reputation points
2020-08-26T06:26:25.047+00:00

Dear Experts,

I have this Azure SQL DB and I can see few tables that have their ID column using the uniqueidentifier column. Also, these tables have the primary key clustered defined on this ID column which is leading to heavy fragmentation. The plan is to use newsequentialID constraint on these ID columns as the ID is generating random values at the moment.

After enforcing the newsequentialID constraint on the ID column,
using below command

ALTER TABLE [dbo].[Scan] ADD DEFAULT (newsequentialid()) FOR [Id]
GO

I performed new inserts using T-SQL directly on the Azure SQL DB and I can see new GUID's getting created that are sequential in nature. However, when the application runs on the application side, it doesn't seem to generate sequential ID's in the database but it is rather random. Is there anything additional that needs to be performed on the application side to enforce this sequential ID function. The application team is using EntityFramwework 6.0. Could you please advise if I am missing something?

Thanks

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike Ubezzi 2,776 Reputation points
    2020-08-27T05:00:54.227+00:00

    Hi @mo boy - Put simply, the NEWSEQUENTIALID (Transact-SQL) function is dependent upon a single Windows host, since being restarted, to maintain a consistent sequence as NEWSEQUENTIALID is a wrapper over the Windows UuidCreateSequential function, with some byte shuffling applied. This is the scenario with SQL Server installed on Windows Server in an on-premise environment. That SQL Server will sequentially generate GUIDs until it is restarted where it will then pick-up at another starting point and start generating a GUID sequence but in another block or GUID range.

    The UuidCreateSequential function has hardware dependencies. On SQL Server, clusters of sequential values can develop when databases (such as contained databases) are moved to other computers. When using Always On and on SQL Database, clusters of sequential values can develop if the database fails over to a different computer.

    With Azure SQL Database instances running as nodes in a clustered infrastructure, and without the pinning concept available like with VMs, the NEWSEQUENTIALID function will never maintain any sequential algorithm as you desire...however! I found an elegant solution by Anuj Chaudhary using NEWSEQUENTIALID() with Entity Framework and SQL Azure.

    In his example, he is using Code First Migrations which places [DatabaseGenerated(DatabaseGeneratedOption.Identity)] on the GUID column in EF and will generate a database creation script with the following attribute on the GUUD column: c.Guid(nullable: false, identity: true).

    His solution was to change this to: c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()") which negates the inconsistency in the GUID sequence in a multi-host environment.

    I have not attempted to reproduce this but can do so should you experience any issues with the information I have provided. Please let me know if you have any additional questions or if this resolves your issue.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.