NEWSEQUENTIALID behavior changed from Apr CTP to June CTP

There may be a bug in the June CTP version of NEWSEQUENTIALID.

NEWSEQUENTIALID had very predictable behavior in the Apr CTP build. It incremented in obvious, clear, consistent amounts. Doing inserts right after another or having widely range time intervals between inserts made no difference.

With the June CTP build, things are not as predictable and do not appear to be sequential. The following output shows the guids that were created. The test plan inserted 10 rows, waited some varying interval of time, then inserted another 10 rows, etc. until all rows were inserted.

Waiting a period of time between inserts of 10 rows appeared to introduce gaps into the sequence. Additionally, the last group of 10 inserts appears to have guid values that fill in some of the gap left by the previous inserts.

30608b57-5dee-d911-83a8-0003ff3ba433 insert 10 rows
31608b57-5dee-d911-83a8-0003ff3ba433
...
39608b57-5dee-d911-83a8-0003ff3ba433 so far so good, wait before more inserts
80ab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
89ab5672-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
8aab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
93ab5672-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
94ab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
9dab5672-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
c0430490-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
c9430490-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
a0143ddb-5dee-d911-83a8-0003ff3ba433 insert 10 rows; appears to be filling in a gap
...
a8143ddb-5dee-d911-83a8-0003ff3ba433

This looks like it could be a bug becuase NEWSEQUENTIALID() values are supposed to be allocated in strictly increasing order. Can you send me a repro? Thanks.

Eric
e h a n s@.microsoft.com|||

Code was sent to Eric Hanson by email. It is also shown below:

USE AdventureWorks
GO

CREATE TABLE dbo.SEQTEST(
[ID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
[Name] [nchar](50) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_SEQTEST] PRIMARY KEY CLUSTERED
)
GO

Make a console application using the following code. Run the console application several times.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace NEWSEQUENTIALID
{
class Program
{
static void Main(string[] args)
{
Inserter lInsert = new Inserter();

for (int i = 0; i < 10; i++)
lInsert.InsertRow(String.Format("batch " + DateTime.Now.ToLongTimeString() + " insert number {0}", i));
}
}
public class Inserter
{
private static string _insertCommand =
"INSERT INTO SEQTEST (Name) OUTPUT INSERTED.ID Values(@.Name)";

public bool InsertRow(string Name)
{
SqlConnection lConnection = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=AdventureWorks");
lConnection.Open();

SqlCommand lInsertCmd = new SqlCommand(_insertCommand, lConnection);
lInsertCmd.Parameters.AddWithValue("Name", Name);

IDataReader lResult = lInsertCmd.ExecuteReader();

while (lResult.Read())
{
Console.WriteLine("ID: {0} Name: {1}", lResult.GetGuid(0).ToString(), Name);
}

lConnection.Close();

return true;
}
}
}

|||It appears that although the behavior may have indeed changed from the April to June CTPs, everything is still working properly. It is probably not wise to try to infer guid sort order by visually inspecting the character representations of the guids. If someone could confirm this by explaining the character representation of and also the internal structures of guids, that would be great.

NEWSEQUENTIALID in the April CTP consistently produced results that were very obviously increasing by looking at the character representations of the guids. The character pattern was completely predictable.

NEWSEQUENTIALID in the June CTP generates results that actually do follow an ascending sort order in SQL as evidenced by ORDER BY. The difference in behavior is that the character representation of the guids does not obviously show the ascending sort when casually inspecting the characters.|||

NEWSEQUENTIALID is not meant to generate gap-less ranges. It's only guarantee is that it will generate strictly increasing values.

As for the part of your repro where you observed that the values did not appear to be increasing. We defined "increasing" in different terms than the character value you see. This is because SQL Server implements it's own comparison semantics on GUIDs.

If you read the first 4 bytes of your GUID value from right to left, you'll notice that they are in strictly increasing order.

If I copy paste your generated GUIDS, and reverse the first DWORD, we see:
578b6030-5dee-d911-83a8-0003ff3ba433 insert 10 rows
578b6031-5dee-d911-83a8-0003ff3ba433
...
578b6039-5dee-d911-83a8-0003ff3ba433 so far so good, wait before more inserts
7256ab80-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
7256ab89-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
7256ab8a-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
7256ab93-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
7256ab94-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
7256ab9d-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
900443c0-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
900443c9-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
db3d14a0-5dee-d911-83a8-0003ff3ba433 insert 10 rows; appears to be filling in a gap
...
db3d14a8-5dee-d911-83a8-0003ff3ba433

Which better describes how we define increasing.

I will check with documentation folks to see if it's possible to be clearer on this.

Let me know if you need more details,

Thanks
-Mat
mathh-at-microsoft-dot-com