Create md5 hash-code using assemblies in SQL Server 2005

It's very simple create md5 hash function in such languages like C# or VB.Net. But in some cases it's nessesary to implement md5-hash algorithm on SQL Server. Unfortunally, SQL Server does not have hash function like md5 and writing T-SQL script is not good idea too. Performance of such script will be very low.

One way to solve this problem is to create .net assembly and attach it to SQL Server instance. Ok, let's do it step by step:

1. Create new project in visual studio. Choose class library template, enter project name and click Ok.

2. Write function that implement md5 hash-code. For this purpose you need add assembly "System.Security.Cryptography" in your project. Listing below show md5 function.

using System.Security.Cryptography;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
namespace Md5Hash
{
  public class Md5Class 
  {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HashString(SqlString value, out SqlString result)
    {
      string str = value.ToString().Trim();
      HashAlgorithm mhash = mhash = new MD5CryptoServiceProvider();
      byte[] bytValue = System.Text.Encoding.UTF8.GetBytes(str);
      byte[] bytHash = mhash.ComputeHash(bytValue);
      mhash.Clear();
        
      StringBuilder sBuilder = new StringBuilder();
      // Loop through each byte of the hashed data 
      // and format each one as a hexadecimal string.
      for (int i = 0; i < bytHash.Length; i++)
      {
         sBuilder.Append(bytHash[i].ToString("x2"));
      }
      // Return the hexadecimal string.
      result = sBuilder.ToString();
    }
  }
}

If you want to use your assembly methods over SQL Server stored procedures, you should folow by next rules:
   - your methods must be implemented as a public static methods on a class in your assembly;
   - your methods must be declared as void or return integer value (in my example I declare method as void);
   - number of parameters must be the same as in stored procedures
   - all parameters must be declared according to SQL Server data types (see MSDN article http://msdn.microsoft.com/en-us/library/ms131092(v=SQL.90).aspx)

Let's back to out listing. We have class called "MD5Hash". This class have only one method "HashString" marked with attribute [Microsoft.SqlServer.Server.SqlProcedure].  By this atribute we define that out method will be used like a stored procedure.
Method "HashString" takes two arguments:

• value - SqlString data type parameter which hash-code we need to return
• result - SqlString pass-by-reference data type parameter which stored procedure returning through an OUTPUt argument

Build our project.

3. Now we should create stored procedure on the SQL Serve side. For using assemblies in T-SQL we must create assembly object in SQL Server database. The folowing example shows how to do it:

create assembly a_md5Hash
from '\\server\SolutionFolder\bin\Debug\MD5Hash.dll'
go

This SQL-statement create sql assembly based on out .net class libraly. Next step is to create stored procedure which will use our assembly

create procedure dbo.sp_md5Hash (@value nvarchar(20), @return nvarchar(max) output)
as external name a_md5hash.[Md5Hash.Md5Class].HashString

Argument as external name assembly_name.class_name.method_name specifies the method of a .Net Framefork assembly for a CLR stored procedure to reference. Parameters:

1. assembly_name is a name of our SQL Server assembly object. 
2. class_name is a namespace and class name of our .net library
3. method_name is a name of md5 class method in .net library

4. Ok. We have done it! Let's try to get hash-code over our stored procedure:

declare @res nvarchar(max)
exec dbo.sp_md5Hash 'Hello World!', @res output
select @res

Well I've got 86fb269d190d2c85f6e0468ceca42a20. Good luck!