Generates a name-based UUID, as described in RFC 4122 section 4.3

fn_sys_GetUuidV5.sql

IF OBJECT_ID('fn_sys_GetUuidV5') IS NOT NULL DROP FUNCTION fn_sys_GetUuidV5
GO
-- SELECT dbo.fn_sys_GetUuidV5(CONVERT(UNIQUEIDENTIFIER, 'E11EAC0E-4D75-4567-BA60-683D357A9227'), 'Test42'), '73CF5B24-114A-5A5B-837C-64CF22468258'
-- SELECT dbo.fn_sys_GetUuidV5(CONVERT(UNIQUEIDENTIFIER, '6ba7b810-9dad-11d1-80b4-00c04fd430c8'), 'www.terraform.io'), 'A5008FAE-B28C-5BA5-96CD-82B4C53552D6'
-- SELECT dbo.fn_sys_GetUuidV5(CONVERT(UNIQUEIDENTIFIER, '6ba7b810-9dad-11d1-80b4-00c04fd430c8'), 'Проба'), 'E40F6EF0-210D-5F5B-B2B1-CABD47B5CBD9'

CREATE FUNCTION fn_sys_GetUuidV5 (
			@Namespace		SQL_VARIANT
			, @Name			SQL_VARIANT
) RETURNS UNIQUEIDENTIFIER
WITH SCHEMABINDING AS
BEGIN
	DECLARE		@Result VARBINARY(MAX)

	IF			SQL_VARIANT_PROPERTY(@Namespace, 'BaseType') = 'uniqueidentifier'
	BEGIN
				SELECT		@Result = CONVERT(VARBINARY(16), @Namespace)

				SELECT		@Result = SUBSTRING(@Result, 4, 1) + SUBSTRING(@Result, 3, 1) + SUBSTRING(@Result, 2, 1) + SUBSTRING(@Result, 1, 1)
									+ SUBSTRING(@Result, 6, 1) + SUBSTRING(@Result, 5, 1)
									+ SUBSTRING(@Result, 8, 1) + SUBSTRING(@Result, 7, 1)
									+ SUBSTRING(@Result, 9, 10)
	END
	ELSE IF		SQL_VARIANT_PROPERTY(@Namespace, 'BaseType') IN ('nchar', 'nvarchar')
	BEGIN
				SELECT		@Result = CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), CONVERT(NVARCHAR(MAX), @Namespace) COLLATE Latin1_General_100_CI_AI_SC_UTF8))
	END
	ELSE		SELECT		@Result = CONVERT(VARBINARY(MAX), @Namespace)

	IF			SQL_VARIANT_PROPERTY(@Name, 'BaseType') IN ('nchar', 'nvarchar')
	BEGIN
				SELECT		@Result = @Result + CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), CONVERT(NVARCHAR(MAX), @Name) COLLATE Latin1_General_100_CI_AI_SC_UTF8))
	END
	ELSE		SELECT		@Result = @Result + CONVERT(VARBINARY(MAX), @Name)

	SELECT		@Result = SUBSTRING(HASHBYTES('SHA1', @Result), 1, 16)

	DECLARE		@Variant VARBINARY(1) = SUBSTRING(@Result, 9, 1) & 63 | 128
	DECLARE		@Version VARBINARY(1) = SUBSTRING(@Result, 7, 1) & 15 | 80

	SELECT		@Result = SUBSTRING(@Result, 1, 6) + @Version + SUBSTRING(@Result, 8, 1) + @Variant + SUBSTRING(@Result, 10, 7)
	
	SELECT		@Result = SUBSTRING(@Result, 4, 1) + SUBSTRING(@Result, 3, 1) + SUBSTRING(@Result, 2, 1) + SUBSTRING(@Result, 1, 1)
						+ SUBSTRING(@Result, 6, 1) + SUBSTRING(@Result, 5, 1)
						+ SUBSTRING(@Result, 8, 1) + SUBSTRING(@Result, 7, 1)
						+ SUBSTRING(@Result, 9, 10)
				
	RETURN		CONVERT(UNIQUEIDENTIFIER, @Result)
END
GO

GRANT EXEC ON fn_sys_GetUuidV5 TO Dreem
GO