Generation of a sequential GUID on SqlServer

SequentialGUID_SqlServer.sql

-- Sequential GUID generation for MS SqlServer (2005+)
-- Can be used in DEFAULT expression
-- idea based on http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

-- helper view required due to usage limitations of crypt_gen_random inside UDF
CREATE VIEW dbo.RandomBytesForSequentialGuidHelperView
AS
	SELECT CRYPT_GEN_RANDOM(10) randomResult
GO

-- UDF
CREATE FUNCTION dbo.sequential_guid()
RETURNS uniqueidentifier
BEGIN

DECLARE @currentUTC datetime;
set  @currentUTC = GETUTCDATE();

DECLARE @epochBegin datetime;
set @epochBegin = CONVERT(DATETIME, '19700101');

DECLARE @daysDiff bigint;
set @daysDiff = datediff(day, @epochBegin, @currentUtc);
DECLARE @helptime datetime
set @helpTime = dateadd(day, @daysDiff, @epochBegin);

DECLARE @millisecondsDiff bigint;
set @millisecondsDiff = datediff(ms, @helpTime, @currentUtc);

DECLARE @totalMilliseconds bigint;
set @totalMilliseconds = @daysDiff * (24*60*60*1000) + @millisecondsDiff;

declare @crypt varbinary(10);
select @crypt = randomResult from RandomBytesForSequentialGuidHelperView;

declare @guid varchar(34);
set @guid = convert(char(34), substring(convert(varbinary(8), @totalMilliseconds), 3, 6) + @crypt, 1)

return CONVERT(uniqueidentifier, 
	substring(@guid, 3, 8) + '-' + 
	substring(@guid, 11, 4) + '-' + 
	substring(@guid, 15, 4) + '-' + 
	substring(@guid, 19, 4) + '-' + 
	substring(@guid, 23, 12));
END


-- uniqueness tested via:
declare @t  table (ids varchar(36) not null unique);

insert into @t
select dbo.sequential_guid() ids from sys.all_objects

select ids from @t

-- Hint:
-- even so there is NEWSEQUENTIALID() it has the limitation that it can be only used with uniqueidentifier columns
-- this UDF can also be used with char/binary columns.
-- also: according to MSDN, NEWSEQUENTIALID looses overall seqentialness if you reboot your server
-- >> "After restarting Windows, the GUID can start again from a lower range, but is still globally unique."
-- see https://msdn.microsoft.com/library/ms189786(v=sql.105).aspx