deploy into SQL Server

[pethopal]

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

[richardtallent]

For a project running on SQL Server, I've been using a .NET CLR function for years, and it works great. It's not included here because .NET CLR projects are a little weird and wouldn't work well in a solution that I wanted to be .NET Standard.

For a newer project, I instead just replicated the same logic in a T-SQL function, so I get compatible COMB values whether I'm generating them in .NET or in SQL Server. I'm using Azure DB for this project, which IIRC doesn't support CLR functions, and using T-SQL for something this trivial is also far faster than making a call into .NET code (which itself was plenty fast). Here's the function:

CREATE FUNCTION [dbo].[COMB](
    @date datetime,
    @guid uniqueidentifier
) RETURNS uniqueidentifier AS 
BEGIN
    DECLARE @unixTimestamp BIGINT;
    SET @unixTimestamp = DATEDIFF_BIG(MILLISECOND,{d '1970-01-01'}, @date);
    RETURN CAST(
        CAST(@guid  AS BINARY(10))
        + CAST(@unixTimestamp AS BINARY(6)
        ) AS UNIQUEIDENTIFIER);
END

You just pass it the timestamp you want to use (say, GETUTCDATE()) and the base GUID value you want to use (e.g., NEWID()). Oddly enough, you can't call NEWID() directly inside of a function (I think functions are intended to be deterministic?). I did find a workaround, but it required an additional function and a view:

CREATE VIEW [dbo].[vw_guid] AS SELECT NEWID() AS guid
go
CREATE FUNCTION [dbo].[NEWCOMB]() RETURNS UNIQUEIDENTIFIER AS
BEGIN
    DECLARE @guid UNIQUEIDENTIFIER;
    SELECT @guid = guid FROM vw_guid;
    RETURN dbo.COMB(GETUTCDATE(), @guid)
END

That will give you a function that requires no parameters and generates a COMB that is compatible with the default SQL Server provider (the one that uses the Unix timestamp).

[pethopal]

Thank you very much for the TSQL function idea! Great, I will try.