It would be beneficial if it can be easily deployed in SQL Server.
Do you see any disadvantages/cons not to do that?

For SqlServer Sequential Guid creation I have:

public static class SeqGuid
{
    private static ICombProvider SqlNoRepeatCombs = new SqlCombProvider(new UnixDateTimeStrategy(), new UtcNoRepeatTimestampProvider().GetTimestamp);

    public static Guid Create()
    {
        return SqlNoRepeatCombs.Create();
    }
}

How to configure the same for binary order ?

In SQL server I can check two GUID if one is lower. (So if one is created before.)

I needed the same functionality in C#
Do you have a suggestion how can I do that?

Thanks for advance,
Best Regards, PP

After upgrading to .NET Core 1.0.1 and migrating the project.json files to .csproj, I'm no longer able to successfully use dotnet test to run unit tests.

It says Starting test execution, please wait..., then appears to hang for a few minutes, then responds with:

Testhost process exited with error: A fatal error was encountered.
The library 'libhostpolicy.dylib' required to execute the application
was not found in '[foo]/test/RT.Comb.Tests/bin/Debug/netstandard1.5'.

where [foo] is the root solution directory.

I would appreciate if anyone could confirm the same issue on OS X or other platforms, or please send a PR if you have a fix!

If it matters, I still want the library to target .NET Standard 1.2, since I want to remain compatible with .NET 4.5.1 and apparently we can't multi-target that separately on OS X anymore.

ICombProvider myCombProvider = new SqlCombProvider(new SqlDateTimeStrategy());

This is too much work and totally leaks all kinds of implementation details.

You really should have a class that behaves something like

public class Comb

public static ICombProvider Sql { get; } = new SqlCombProvider(new SqlDateTimeStrategy());
public static ICombProvider Unix  { get; } = new whatever;

etc.

There's probably almost never a reason a person needs to actually touch your construction. Just give people the defaults in a statically available manner.

Hi, newbie question here.

I'm wondering if it's possible to convert a timestamp to the exact original comb guid.

SqlCombProvider provider = new(new UnixDateTimeStrategy(), new UtcNoRepeatTimestampProvider().GetTimestamp);

Guid comb = provider.Create();
DateTime timestamp = provider.GetTimestamp(comb);
Guid comb1 = provider.Create(timestamp);
Guid comb2 = provider.Create(comb, timestamp);

Console.WriteLine(comb.Equals(comb1)); // false
Console.WriteLine(comb.Equals(comb2)); // true, but possible only from timestamp?

If I understand correctly, documentation explain that first bytes of guid is random. There is a way to obtain the original full comb id only from timestamp?

Thank you so much.

Hey thanks for this library! I have been digging into this Comb Guid stuff lately and really liked the way this is implemented :)

Would be nice though to have a package in order to make it easier to add a comb provider to DI. Similar as the packages in ASP.NET Core (pay-to-play), we could have something like RT.Comb.AspNetCore.

Since you are the owner, not sure if it makes sense to have someone else do the extension. I also saw it needs to be bumped to .NET Standard 2.0. If you need help with that I can jump in :).

DNX is officially too much of a pain in the ass to deal with. I need to convert this to a traditional class library so I can actually use it.

I use RT.Comb on the back-end, however I also need it on the browser, so I wrote the following JavaScript function:

function newSequentialGuid() {
  var ms = new Date().getTime().toString(16).padStart(12, "0");
  var a = new Uint8Array(10);
  crypto.getRandomValues(a);
  a[0] = a[0] & 15 | 64;
  a[2] = a[2] & 63 | 128;
  return ms + Array.prototype.map.call(a, x => x.toString(16).padStart(2, "0")).join("");
}

Please, could you confirm it is equivalent to RT.Comb.Provider.Postgre.Create().ToString("N") ?

Hello,

I'm are using Entity Framework to select data from DB. The PK of the table is a COMB GUID.
I would like to get the list of records for a date using the COMB PK for efficiency.
Following your doc, I'm able to generate the COMB "boundaries" of the day in SQL Server side but not in C#.
e.g. >= 2023-10-11 00:00:00.000 to <= 2023-10-11 23:59:59.999 or better < 2023-10-12 00:00:00
=>

'00000000-0000-0000-0000-018B1C087C00' to <= FFFFFFFF-FFFF-FFFF-FFFF-018B212ED800 or < 00000000-0000-0000-0000-018B212ED800

I could do it by splitting/concatating string but is there another way?

Tx.

Michael

Using SYSUTCDATETIME() instead of GETUTCDATE()

First of all, thank you for creating the library.

The readme has the following examples for generating a COMB in T-SQL

Creating a COMB uniqueidentifier in T-SQL with the current date and time:

DECLARE @now DATETIME = GETUTCDATE();
DECLARE @daysSinceEpoch BIGINT = DATEDIFF(DAY, '1970-1-1', @now);
DECLARE @msLeftOver INT = DATEDIFF(MILLISECOND, DATEADD(DAY, @daysSinceEpoch, '1970-1-1'), @now);
SELECT CAST(
        CAST(NEWID() AS BINARY(10))
        + CAST(@daysSinceEpoch * 24 * 60 * 60 * 1000 + @msLeftOver AS BINARY(6))
    AS UNIQUEIDENTIFIER);

Or on MSSQL 2016/Azure:

SELECT CAST(
        CAST(NEWID() AS BINARY(10))
        + CAST(DATEDIFF_BIG(MILLISECOND, '1970-1-1', GETUTCDATE())
    AS BINARY(6)) AS UNIQUEIDENTIFIER);

These use GETUTCDATE() which returns a datetime with 1/300s precision.

Instead use SYSUTCDATETIME() which returns a datetime2(7) which for this purpose will give you 1/1000s precision as per what you would get with the UnixDateTimeStrategy in C#.

datetime2 and SYSUTCDATETIME() were introduced in SQL Server 2008 so is usable in both above examples.

Suggested change:

Creating a COMB uniqueidentifier in T-SQL with the current date and time:

DECLARE @now DATETIME2(7) = SYSUTCDATETIME();
DECLARE @daysSinceEpoch BIGINT = DATEDIFF(DAY, '1970-1-1', @now);
DECLARE @msLeftOver INT = DATEDIFF(MILLISECOND, DATEADD(DAY, @daysSinceEpoch, '1970-1-1'), @now);
SELECT CAST(
        CAST(NEWID() AS BINARY(10))
        + CAST(@daysSinceEpoch * 24 * 60 * 60 * 1000 + @msLeftOver AS BINARY(6))
    AS UNIQUEIDENTIFIER);

Or on MSSQL 2016/Azure:

SELECT CAST(
        CAST(NEWID() AS BINARY(10))
        + CAST(DATEDIFF_BIG(MILLISECOND, '1970-1-1', SYSUTCDATETIME())
    AS BINARY(6)) AS UNIQUEIDENTIFIER);

UDF to generate a COMB

The below is separate but I'd also like to contribute a user defined function that I use to make queries simpler to read, at the performance cost of the overhead of calling a function. Don't have to add it to the readme.

/****** Object:  UserDefinedFunction [dbo].[NewComb] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    Return a COMB GUID (sortable uniqueidentifier). See https://github.com/richardtallent/RT.Comb
-- =============================================
CREATE FUNCTION [dbo].[NewComb]()
RETURNS uniqueidentifier
AS
BEGIN
    -- Generate a COMB as per https://github.com/richardtallent/RT.Comb documentation
    RETURN CAST(
        CAST((select new_id from vwGetNewID) AS BINARY(10))
        + CAST(DATEDIFF_BIG(MILLISECOND, '1970-1-1', SYSUTCDATETIME())
    AS BINARY(6)) AS UNIQUEIDENTIFIER);
END
GO

/****** Object:  UserDefinedFunction [dbo].[NewCombFromDateTime] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    Return a COMB GUID (sortable uniqueidentifier). See https://github.com/richardtallent/RT.Comb
-- =============================================
CREATE FUNCTION [dbo].[NewCombFromDateTime]
(
    @datetime datetime2(3)
)
RETURNS uniqueidentifier
AS
BEGIN
    -- Generate a COMB as per https://github.com/richardtallent/RT.Comb documentation
    RETURN CAST(
        CAST((select new_id from vwGetNewID) AS BINARY(10))
        + CAST(DATEDIFF_BIG(MILLISECOND, '1970-1-1', @datetime)
    AS BINARY(6)) AS UNIQUEIDENTIFIER);
END
GO

/****** Object:  View [dbo].[vwGetNewID] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    View which returns newid()
-- =============================================
CREATE VIEW [dbo].[vwGetNewID] AS SELECT newid() AS new_id
GO

The above script creates two user defined functions (UDFs), NewComb which uses the current time, and NewCombFromDateTime which takes the date/time as a parameter. These return a COMB.

Usable like so:

-- Generate a COMB with the current utc date/time
select dbo.NewComb()

-- or:

-- Generate a COMB with a given utc date/time
declare @now datetime2(3) = sysucdatetime()

select dbo.NewCombFromDateTime(@now)

Note that the script above also creates a view named vwGetNewID which returns newid(). This is a hack to allow the use of newid() in a UDF which isn't allowed normally. Alternatively, you could eliminate the view but would need to add a uniqueidentifier parameter in which you pass newid() as a parameter to the function so that it has a guid to work with.

My project is targeting .NET Framework 4.6.1 and when I go to install RT.Comb 2.3.0 I get the following prompt to install a load of dependencies for code that's already part of the .NET standard library:

I think this issue might be fixable by either bumping the version of .NET Standard you target to 2.0 (I'm using Dapper which targets 2.0 and don't have this issue) or adding another entry to <TargetFrameworks> as discussed here.

This library is great especially since it targets .NET Standard and .NET Framework but your latest version specifically targets .NET Framework v4.6 which most people have not adopted yet. Would it be possible to lower the runtime version down to v4.5.1? It should be okay since you are not using anything specific from the newer runtime.

Hi,
Thanks for the awesome library. I don't necessarily have any issue with this library, but I was hoping your expertise on the matter would be able to help me.

I've been generating the SQL Server legacy COMB GUIDs for primary key values in a product for quite some time now (millions of records). We are currently evaluating the possibility of migrating the product from SQL Server to PostgreSQL and with this comes migrating all of the existing data.

My concern is that the existing comb GUIDs that were generated for SQL Server are of a different sort algorithm than what would have been for PostgreSQL. My guess is that generating new values under the PostgreSQL algorithm is not going to create the desired incremental GUID values based on the already existing IDs that would be migrated. New values would likely be somewhere in the middle of the index and will lead to fragmented tables.

Does that make sense?

Could you recommend any way of dealing with a situation like this? Perhaps there is some way I can seed the new PostgreSQL comb generator to always generate incremental GUIDs after the "last" (in terms of PostgreSQL sorting) one that was generated from the previous SQL Server legacy algorithm?

Thanks!

When using RT.Comb.Provider.PostgreSql , the generated Guids are not in sorted order in Postgresql 13.3.

            using var context = new TDbContext();
            for (int i = 0; i < 20; i++)
            {
                var entity = new CombGuidEntity
                {
                    Id = Provider.PostgreSql.Create(),
                    Sequence = i + 1
                };
                context.Add(entity);
            }
            context.SaveChanges();

Hi Richard,
I have been using for the RT.Comb GUIDs very successfully both in OLTP, and OLAP projects
Update: even with tables having more hundreds million rows! Both row, and clustered columnstore indexed tables in SQL Server!
So I think it should be a MUST to use this RT.Comb GUID if you want to use it for PK. Which has big advantages!)

All the time these GUIDs were generated by us, I mean, at our side: so we were sure that these are ordered.

But now I will be dependent on an outer application.
The question is whether I can check/test whether this GUID is RT.Comb one?
(If it will not be the case, I will substitute this with an RT.Comb one, so at the database side everything will be ordered.)

Thanks in advance,
Pál

Hi, after reading https://davecallan.com/generating-sequential-guids-which-sort-sql-server-in-net/

I would like to introduce in my SQL Server tables Id generated as sequential Guid.

I just have applications in production with populated tables and normal Guid IDs, may I have any issue?

The T-SQL in the README for extracting the DateTime value from the COMB is off (at least for the SQL Server 2014).

Currently, this is in the README:
CAST(SUBSTRING(CAST(0 AS binary(2)) + CAST(value AS binary(16), 10, 6) AS datetime)

The Substring function's index is 1-based instead of 0-based. Additionally, I think the 0-fill at the beginning needs to be prepended separately from the Substring of the value. Here is the revised version:
CAST(CAST(0 AS binary(2)) + SUBSTRING(CAST(value AS binary(16)), 11, 6) AS datetime)

Additionally, here is a quick script to verify everything in SQL Server.

-- Quick verification of extracting date/time from COMB GUID value.
DECLARE @Today AS datetime
DECLARE @ToCombGuid AS uniqueidentifier
DECLARE @FromCombGuid AS datetime
DECLARE @IsMatch AS bit

SET @Today = GETUTCDATE()
SET @ToCombGuid = CAST(CAST(NEWID() AS binary(10)) + CAST(@Today AS binary(6)) AS uniqueidentifier)
SET @FromCombGuid = CAST(CAST(0 AS binary(2)) + SUBSTRING(CAST(@ToCombGuid AS binary(16)), 11, 6) AS datetime)
SET @IsMatch = IIF(@Today = @FromCombGuid, 1, 0)

select @Today AS Today, @ToCombGuid AS ToCombGuid, @FromCombGuid AS FromCombGuid, @IsMatch

This library is a great idea! I've got code like it scattered around as well.

Hi, I used RT.Comb succesfully many years. (In SQL Server these Guids can even be used as Primary Keys)

For efficient querying purposes can I generate a Guid for a Specific DateTime.
(Note I will use it for only in SQL Where '>', '<' clause.

Thanks...

Now that I'm actually able to test this library in PostgreSql, I see where I'm going wrong on encoding the timestamp so the order is preserved. Marking this so anyone wanting to use this in PostgreSql knows about the issue, I hope to have it resolved soon.

This code:

ICombProvider SqlCombs = new SqlCombProvider(new SqlDateTimeStrategy());
for (int i = 0; i < 10; i++)
{
    string userEmail = $"user{i}@c.com";
    if (!context.Users.Any(a => a.Email == userEmail))
    {
        User user = new User
        {
            UserId = SqlCombs.Create(), // I tried also Create(DateTime.Now) but it didn't help
            Email = userEmail,
            TimeCreated = DateTime.Now 
        };
        context.Users.Add(user);
    } 
}

gives me following rows in Db:

UserId_________________________________________Email______________DateCreated
[email protected]____2017-03-28 15:19:01.4968188
[email protected]____2017-03-28 15:19:01.4991283
[email protected]____2017-03-28 15:19:01.5031330
[email protected]____2017-03-28 15:19:01.5011320
[email protected]____2017-03-28 15:19:01.5051359
...

user4 comes before user3.
I think that you need to have _lastSequence like it is used on the blog (line 87):
http://www.siepman.nl/blog/post/2013/10/28/ID-Sequential-Guid-COMB-Vs-Int-Identity-using-Entity-Framework.aspx

Hi there. I've been using your library for years, so a very big thank you.

I was reading a comment you made here, regarding querying via the timestamp contained in the COMB:

#10 (comment)

Even though I used COMBs on pretty much all tables these days, I also have a date/time of insertion field, because the COMB's timestamp value is unwieldy to query directly.

Which made me question a couple of things:

1. I understand that there is a chance of collision with regards to the timestamp portion of a COMB e.g. if many are generated and inserted at around the same time; is this the only factor that make querying via the timestamp "unwieldy" or are there other factors?
2. Can you recommend a optimum/performant way of querying DB records via a COMB timestamp, or range?
   e.g. all records between timestamp X and Y, from a COMB?
3. If you recommend using a separate date/time insertion field, as well as a COMB, is there any real value to being able to extract the timestamp from the generated COMB? Other than for testing purposes.
   In my mind, half the point of using a COMB was so that I could avoid having to store a separate date time value in the table (rightly or wrongly).

Thank you!

Hello,

Based on the documentation under security and performance, can i use it with MySQL but store the UUID as BINARY(16)?
if not, when you planning to support MySql?

Many Thanks
RA

Proposal

Please change the dependency on M.E.DependencyInjection v7.0 to v6.0 for RT.Comb.AspNetCore v4.0.

Reasoning

All the .NET 6.0 stuff (e.g. ASP.NET Core 6.0) relies on M.E.DependencyInjection v6.0 and using NuGet packages shipped alongside with .NET 7.0 seems like a potentially dangerous move, because nobody can guarantee that the interface surface didn't change between v6.0 and v7.0.