TSQL生成Combguid

Nhibernate实现combguid

/// <summary>
        /// Generate a new <see cref="Guid"/> using the comb algorithm.
        /// </summary>
        private Guid GenerateComb()
        {
            byte[ ] guidArray = Guid.NewGuid().ToByteArray();

            DateTime baseDate = new DateTime( 1900, 1, 1 );
            DateTime now = DateTime.Now;

// Get the days and milliseconds which will be used to build 
//the byte string 
            TimeSpan days = new TimeSpan( now.Ticks - baseDate.Ticks );
            TimeSpan msecs = now.TimeOfDay;

            // Convert to a byte array   
// Note that SQL Server is accurate to 1/300th of a 
// millisecond so we divide by 3.333333 
            byte[ ] daysArray = BitConverter.GetBytes( days.Days );
            byte[ ] msecsArray = BitConverter.GetBytes( ( long ) 
( msecs.TotalMilliseconds/3.333333 ) );

            // Reverse the bytes to match SQL Servers ordering 
            Array.Reverse( daysArray );
            Array.Reverse( msecsArray );

            // Copy the bytes into the guid 
            Array.Copy( daysArray, daysArray.Length - 2, guidArray, 
guidArray.Length - 6, 2 );
            Array.Copy( msecsArray, msecsArray.Length - 4, guidArray, 
guidArray.Length - 4, 4 );

            return new Guid( guidArray );
        }

 

需要在SQL中生成Combguid，没有找到代码。翻了一下

CREATE FUNCTION [dbo].[GenerateCombGuid]()
RETURNS VARCHAR(36)
AS
BEGIN
        DECLARE @guidArray AS VARCHAR(36) = (SELECT TOP 1 id FROM GetGuid)
        DECLARE @basedate AS DATETIME = '1900-01-01'
        DECLARE @basedateTicket AS BIGINT = 599266080000000000
        DECLARE @now AS DATETIME = GETDATE()

        DECLARE @tmp_days_from_0001_to_1900 AS BIGINT = 693595;
        DECLARE @tmp_ticks_per_millisecond AS BIGINT = 10000;
        DECLARE @tmp_ticks_per_day AS BIGINT = 24 * 3600 * 1000
            * @tmp_ticks_per_millisecond;
        DECLARE @tmp_millisecond_of_time AS INT= DATEPART(MILLISECOND, @now)
            + ( DATEPART(SECOND, @now) ) * 1000 + ( DATEPART(MINUTE, @now) )
            * 60000 + ( DATEPART(HOUR, @now) ) * 3600000;
        DECLARE @ticks_of_days AS BIGINT = CAST(@tmp_days_from_0001_to_1900
            + DATEDIFF(DAY, 0, @now) AS BIGINT) * @tmp_ticks_per_day;
        DECLARE @ticks_of_time AS BIGINT = @tmp_millisecond_of_time
            * @tmp_ticks_per_millisecond;
        DECLARE @nowTicket AS BIGINT = @ticks_of_days + @ticks_of_time;

        DECLARE @days AS INT = ( @nowTicket - @basedateTicket ) / 1000 / 60000 / 60 / 24 / 10
        DECLARE @times AS INT = ( @nowTicket - @basedateTicket ) / 1000 / 60000 

        DECLARE @daysArray AS VARBINARY(2)  = CONVERT(VARBINARY(2), @days);
        DECLARE @msecsArray AS VARBINARY(4)  = CONVERT(VARBINARY(4), CAST(@tmp_millisecond_of_time
            / 3.333333 AS BIGINT)); 

        return  SUBSTRING(@guidArray, 1, 23) + '-'
                + CONVERT(VARCHAR(4), @daysArray, 2)
                + CONVERT(VARCHAR(8), @msecsArray, 2)
END

GO

Sqlserver Function中无法直接使用NEWID()，再建立一个View

Create View GetGuid
as
SELECT  newid() id
GO

 

生成100w条记录用时，还行

SELECT NEWID() FROM Nums  --4s
SELECT dbo.[GenerateCombGuid]() FROM Nums --6s