'Using NEWID() when explicitly inserting a value for an ID (key) column mutates ID values for other previously existing rows in the same table

I have a SQL Server database which is maintain through Entity Framework Core. Every time I need a change of the database schema, I just generate a new migration and apply it. Really convenient.

But recently I started to notice something very strange. I have a table / entity / class which looks like this in the code:

public class BaseEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; } = Guid.NewGuid();
    public string OtherCol { get; set; }
} 

and like this when I check the creation scripts on the DB engine itself:

CREATE TABLE [dbo].[MyTable]
(
    [Id] [uniqueidentifier] NOT NULL,
    [OtherCol] [nvarchar](max) NULL

    CONSTRAINT [PK_Bills] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And I am facing a very unexpected and breaking behavior. Every time I try to insert a new row into my table making a reference to a similar script:

INSERT INTO [dbo].[MyTable]([Id],[OtherCol])
VALUES (NEWID(), 'Some value') 

for some of the already existing rows the Id values get changed implicitly somehow. This was quite unexpected and it reproduces in my production databases. How could that be solved? I really need to prevent that from happening.

It is so strange.

1. Imagine I initially have the following content:

530175A6-3F62-4757-9CF0-3A0C01CF01D2 | Value 1
AF5C7F26-1D94-4002-9B10-45D3645415B0 | Value 2

2. Then I try to insert a new row

INSERT INTO [dbo].[MyTable]([Id], [OtherCol]) 
VALUES (NEWID(), 'Value 3') 

3. What you can see now in the table is possible to be (Id for 'Value 1' has changed):

BF523CC9-974B-416C-B45A-BEB70748B8AD | Value 1
AF5C7F26-1D94-4002-9B10-45D3645415B0 | Value 2
7648FDD3-6D8F-44C5-B55A-6919584EBF9D | Value 3

NOTE: What if I start using NEWSEQUENTIALID() from now on? Would it help at least for the future?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source