Done a bit of digging here, using a SQLCLR equivalent to the OPs script (source below)

using System;

using System.Data.SqlTypes;

using System.Text;

using System.Security.Cryptography; // Will need a reference to System.Security

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]

public static String ToBase64String(String str)

{

if (str == null)

{

return str;

}

MD5 md5 = new MD5CryptoServiceProvider();

byte[] columnContents = UnicodeEncoding.Unicode.GetBytes(str);

byte[] hash = md5.ComputeHash(columnContents);

String hashString = Convert.ToBase64String(hash, Base64FormattingOptions.None);

return hashString;

}

}

CREATE FUNCTION [dbo].[ToBase64String](@str [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [MySQLCLR].[UserDefinedFunctions].[ToBase64String]

gives the same results as using the built-in HASHBYTES and XML conversion to base64

DECLARE @STR NVARCHAR(100);

SET @STR='abcdefg';

SELECT dbo.ToBase64String(@str);

WITH source(col) AS (

SELECT HASHBYTES('MD5',@str))

SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("col")))', 'VARCHAR(MAX)') AS Base64String

FROM source;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537