The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
It’s very simple to create an md5 hash function in such languages like C# or VB.NET. But in some cases, it’s necessary to implement md5-hash algorithm on SQL Server. Unfortunately, SQL Server does not have hash function like md5 and writing T-SQL script is not a good idea too. Performance of such script will be very low.
Using the Code
One way to solve this problem is to create a .NET assembly and attach it to SQL Server instance. Ok, let’s do it step by step:
- Create a new project in Visual Studio. Choose class library template, enter project name and click Ok.
- Write function that implements md5 hash-code. For this purpose, you need to add assembly “
System.Security.Cryptography” in your project. Listing below shows md5 function.
public class Md5Class
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);
StringBuilder sBuilder = new StringBuilder();
for (int i = 0; i < bytHash.Length; i++)
result = sBuilder.ToString();
If you want to use your assembly methods over SQL Server stored procedures, you should follow the next rules:
- Your methods must be implemented as
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
- 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 get back to our listing. We have a class called “
MD5Hash”. This class has only one method “
HashString” marked with attribute
[Microsoft.SqlServer.Server.SqlProcedure]. By this attribute, we define that our method will be used like a stored procedure. Method “
HashString” takes two arguments:
SqlString data type parameter which hash-code we need to return
SqlString pass-by-reference data type parameter which stored procedure returning through an
Build our project.
- Now we should create a stored procedure on the SQL Server side. For using assemblies in T-SQL, we must create assembly object in SQL Server database. The following example shows how to do it:
create assembly a_md5Hash
This SQL-statement creates a SQL assembly based on our .NET class liberally. The next step is to create a 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 Framework assembly for a CLR stored procedure to reference. Parameters:
class_name is a namespace and class name of our .NET library.
method_name is a name of md5 class method in .NET library.
assembly_name is a name of our SQL Server assembly object.
- 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!
- 7th January, 2011: Initial post