Why is default newsequentialid() not working?

Question

I have a table with a default id of newsequentialid, but the values being generated are not sequential.

[Id] UNIQUEIDENTIFIER DEFAULT (newsequentialid()) NOT NULL

5f3ed690-5110-46c9-2ea8-08d68135eb22
73709d10-81b8-4fcd-2ea9-08d68135eb22
c2320a90-8cd9-4fc7-2eaa-08d68135eb22
a8b2a5a1-0e61-4562-2eab-08d68135eb22
fed58540-42d6-4644-2eac-08d68135eb22
de292204-c00c-49d1-2ead-08d68135eb22
59fe8541-9829-4fa1-2eae-08d68135eb22
90007035-5247-44a9-2eaf-08d68135eb22
dcdbc8dd-a409-435f-2eb0-08d68135eb22
3feba54b-9236-4dfe-2eb5-08d68135eb22

I'm populating the rows like this:

if (!dbContext.MyTable.Any())
{
    for (int i = 0; i < 100; i++)
    {
        dbContext.MyTable.Add(new MyModel {  Col1= "test", Col2= "test", Created = DateTime.UtcNow });
    }

    await dbContext.SaveChangesAsync();
}

Using SQL Server Express 2017 14.0.2002

I didn't when creating these, I used a for loop to seed with some test data. — Oliver, Jan 23 '19 at 13:51
Are you sure that your inserts use the default value? You (or someone else) can insert passing any guid — sepupic, Jan 23 '19 at 13:52
You should update your question with the complete repro (the code with inserts included) and @@version of your server — sepupic, Jan 23 '19 at 13:56
The repro code is particularly important because, for starters, the order of any list of values you retrieve through a `SELECT` query is undefined if you don't also have an `ORDER BY`. (Contrary to popular belief, it is not true that you're guaranteed to get "the insert order", "the index order" or whatever else people confuse for an actual order.) — Jeroen Mostert, Jan 23 '19 at 14:03
So your code inserts 100 values, but you showed us only 10. How did you retrive them, as select top 10 without any order by? As Jeroen Mostert said, you should use ORDER BY id when select from your table — sepupic, Jan 23 '19 at 14:20
When I insert from a sql query it works, but when I insert from my application in code (ef framework dbcontext) it doesn't work. How do I setup my dbcontext to use sequentialid? — Oliver, Jan 23 '19 at 14:21
@sepupic yes I tried select order by, it doesn't make a difference. This seems to be an issue how I'm inserting my data through ef datacontext. It's working if I use a sql query, but not with my example code. — Oliver, Jan 23 '19 at 14:27
Entity Framework is always a joy. I suggest seeing what it's actually doing by hooking up a Profiler trace. It might be surprising. (It's not beyond confabulating default values in cases where you didn't ask for them, for example.) — Jeroen Mostert, Jan 23 '19 at 14:30
I would ask why you are uniqueidentifier if you are just sticking newsequentialid in there. It lost all the benefits and kept all the bad stuff. I would think an identity provides you the same feature but takes a lot less storage and is easier for things like data analysis where you have to type in the key. — Sean Lange, Jan 23 '19 at 14:46
@Sean Lange Just testing here. The project design asks for GUID's, so hoping to benefit from a performance boost with newsequentialid. — Oliver, Jan 23 '19 at 14:55
Ahh gotcha. So if just testing it matters even less what the values are right? What is the select statement you are using to get the output? — Sean Lange, Jan 23 '19 at 14:57
Well it matters that I would expect them to be sequential and they're not... — Oliver, Jan 23 '19 at 15:10
As Jeroen Mostert said, the problem likely is in code generated by your EF, as the code executed directly does not produce the same result. Did you use Profiler to catch what exactly your EF sends to server? — sepupic, Jan 24 '19 at 07:34

score 1 · Answer 1 · answered Jan 23 '19 at 13:53

They look sequential to me:

CREATE TABLE guids
(
    id int IDENTITY(1,1) PRIMARY KEY,
    guid uniqueidentifier DEFAULT (newsequentialid()) NOT NULL UNIQUE
)

DECLARE @i int = 0
WHILE @i < 10
BEGIN
    INSERT INTO guids DEFAULT VALUES
    SET @i = @i + 1
END

SELECT *
FROM guids
ORDER BY guid

Output:

+----+--------------------------------------+
| id |                 guid                 |
+----+--------------------------------------+
|  1 | C46ABC87-151F-E911-8566-00155D08A60F |
|  2 | C56ABC87-151F-E911-8566-00155D08A60F |
|  3 | C66ABC87-151F-E911-8566-00155D08A60F |
|  4 | C76ABC87-151F-E911-8566-00155D08A60F |
|  5 | C86ABC87-151F-E911-8566-00155D08A60F |
|  6 | C96ABC87-151F-E911-8566-00155D08A60F |
|  7 | CA6ABC87-151F-E911-8566-00155D08A60F |
|  8 | CB6ABC87-151F-E911-8566-00155D08A60F |
|  9 | CC6ABC87-151F-E911-8566-00155D08A60F |
| 10 | CD6ABC87-151F-E911-8566-00155D08A60F |
+----+--------------------------------------+

Note that the first byte on the string representation is sequential: C4, C5, C6, C7, ...

They "look sequential" in your test, but in the OP output they are not sequential — sepupic, Jan 23 '19 at 13:54
@sepupic, then I guess the op is not using the default value — Jesús López, Jan 23 '19 at 13:57
@JesúsLópez the OP is using the default value. Look at the first line of code. — David Klempfner, Jun 27 '22 at 05:53

score 1 · Accepted Answer · answered Jan 23 '19 at 18:59

1

I figured out I needed to add the following to the OnModelCreating method in the ApplicationDbContext

modelBuilder.Entity<MyModel>().Property(x=>x.Id).HasDefaultValueSql("NEWSEQUENTIALID()");

answered Jan 23 '19 at 18:59

Oliver

43
6

Why is default newsequentialid() not working?

2 Answers2