Click here to Skip to main content
13,140,591 members (48,009 online)
Click here to Skip to main content
Add your own
alternative version

Stats

19.4K views
Posted 26 Oct 2011

Consistent Hash function for SQL Server and .NET

, 26 Oct 2011
Rate this:
Please Sign up or sign in to vote.
Provides a couple of hash functions (string to int) that will return the same value in C# and T-SQL
Sometimes, you need to do some hashing in T-SQL on the server side and use it in C# or vice-versa.
The algorithm used by the native 'checksum' is not divulged.
Here is a simple string to hash 32 bit function that can be used to get a number from a string.
An optional parameter is available if you want to reduce the result to a shorter number.
Both functions return the same.

T-SQL Function Source
CREATE FUNCTION FnMD5Hash(@SourceString varchar(8000), @Modulo INT = 0)
	RETURNS INTEGER
AS
BEGIN
	IF @Modulo = 0
		RETURN CAST(HashBytes( 'MD5', @SourceString) AS INTEGER)
	ELSE
		RETURN ABS(CAST(HashBytes( 'MD5', @SourceString) AS INTEGER)) % @Modulo
	RETURN 0
END


C# Function Source
static System.Security.Cryptography.MD5CryptoServiceProvider md5Provider =
   new System.Security.Cryptography.MD5CryptoServiceProvider();
// the database is usually set to Latin1_General_CI_AS which is codepage 1252
static System.Text.Encoding encoding = 
   System.Text.Encoding.GetEncoding(1252); 
 
static Int32 SQLStringToHash(string sourceString, int modulo = 0)
{
   var md5Bytes = md5Provider.ComputeHash(encoding.GetBytes(sourceString));
   var result = BitConverter.ToInt32(new byte[] { 
      md5Bytes[15], md5Bytes[14], md5Bytes[13], md5Bytes[12] }, 0);
   if (modulo == 0) return result;
   else return Math.Abs(result) % modulo;
}


T-SQL Test Source
DECLARE @SourceString AS VARCHAR(255) = 'Joyeux Noël'
 
PRINT 'T-SQL Test'
PRINT 'Source: ' + @SourceString
 
PRINT 'Hash:'
PRINT dbo.FnMD5Hash(@SourceString, default)
PRINT 'Hash (0..999):'
PRINT dbo.FnMD5Hash(@SourceString, 1000)


C# Test Source
string sourceString = "Joyeux Noël";
Console.WriteLine("C# Test");
Console.WriteLine("Source: "+sourceString);
Console.WriteLine("Hash: " + SQLStringToHash(sourceString));
Console.WriteLine("Hash (0..999): " + SQLStringToHash(sourceString, 1000));


T-SQL Test Output
T-SQL Test
Source: Joyeux Noël
Hash: -92694766
Hash (0..999): 766


C# Test Output
C# Test
Source: Joyeux Noël
Hash: -92694766
Hash (0..999): 766

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pascal Ganaye
Software Developer (Senior)
France France
I am a French programmer.
These days I spend most of my time with the .NET framework, JavaScript and html.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 26 Oct 2011
Article Copyright 2011 by Pascal Ganaye
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid