Click here to Skip to main content
13,248,766 members (57,479 online)
Click here to Skip to main content
Add your own
alternative version


111 bookmarked
Posted 26 Jan 2004

MD5 Hash SQL Server Extended Stored Procedure

, 7 Mar 2005
Rate this:
Please Sign up or sign in to vote.
An extended stored procedure for SQL Server that implements an optimized MD5 hash algorithm. Very small DLL (barely 7 KB).


This is an extended stored procedure for Microsoft SQL Server 2000 that implements an optimized MD5 hash algorithm. It is intended to work much like the MySQL MD5() function. The algorithm was taken from here. I only added the md5_string() function. The DLL should work for older versions of SQL Server too, although I have not tested that. The source was compiled and tested on Microsoft Visual C++ 6.0 and .NET 2003.


  1. Extract or build the DLL file xp_md5.dll and place it in C:\Program Files\Microsoft SQL Server\MSSQL\Binn (or wherever appropriate). A precompiled DLL is in the Release directory of the source distribution.
  2. Create an Extended Stored Procedure called xp_md5 in the "master" database. Right-click "Extended Stored Procedures" under the master database in the Server Manager and click "New Extended Stored Procedure...". Enter xp_md5 for the "Name" and for the "Path", enter the full path to xp_md5.dll.

    Note: If you want to add it manually:

    USE master;
    EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'
  3. Create a user-defined function for each database in which you plan to use the MD5 procedure. Right-click "User Defined Functions" under the appropriate database(s) and click "New User Defined Function...". Enter the following:
    CREATE FUNCTION [dbo].[fn_md5] (@data TEXT) 
      DECLARE @hash CHAR(32)
      EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
      RETURN @hash
  4. (Optional) Create other user-defined functions to let you specify the data length (for substrings, BINARY and other fixed-width types). In this particular function, we take an IMAGE for input and an optional LENGTH. A negative LENGTH value causes the DLL to try to compute the length of the input automatically (this is the default):
    CREATE FUNCTION [dbo].[fn_md5x] (@data IMAGE, @len INT = -1) 
      DECLARE @hash CHAR(32)
      EXEC master.dbo.xp_md5 @data, @len, @hash OUTPUT
      RETURN @hash



The User-Defined Functions can be used as follows:

-- Sample SQL statement:
-- fn_md5() tries to automatically calculate the length of the input string
SELECT dbo.fn_md5('Hello world!');

-- fn_md5x() takes an optional length arg for substrings, fixed-width types, etc.
SELECT dbo.fn_md5x('Hello world!', 12);

Output for both statements:


XP_MD5: EXEC xp_md5 <@data> [@length = -1] [@hash OUTPUT]

To use the Extended Stored Procedure directly:

-- Sample command:
EXEC master.dbo.xp_md5 'Hello world!'



Or if you want the result saved to a variable instead:

DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 'Hello world!', -1, @hash OUTPUT
PRINT @hash




-- These are just examples for use with the given functions above.
-- Feel free to create your own functions that take the input type you want.
-- Use CAST() with caution, as it may truncate and have other unintended consequences.

SELECT dbo.fn_md5(data) FROM table;

SELECT dbo.fn_md5(CAST(data AS VARCHAR(8000))) FROM table;

-- For IMAGE:
SELECT dbo.fn_md5x(data, DEFAULT) FROM table;

-- For CHAR: (we can use LEN() on this fixed-width type to trim the padding)
SELECT dbo.fn_md5x(data, LEN(data)) FROM table;

-- For NCHAR: (we can use LEN() on this fixed-width type to trim the padding)
SELECT dbo.fn_md5x(CAST(data AS CHAR(4000)), LEN(data)) FROM table;

-- For BINARY:
SELECT dbo.fn_md5x(data, 12) FROM table;

SELECT dbo.fn_md5x(CAST(data AS VARCHAR(8000)), DATALENGTH(data)) FROM table;


For purposes of speed, I did not include any real input data verification (e.g., type checking, data length checking, etc.). I opted to exclude that in order to maximize speed, as I originally wrote this for use in an application that inserts millions of rows at a time. I also know that I'm always calling the procedure properly. If you want to make it more robust - like if you do not know what kind of data will be passed to the function - then I highly recommend you add those safeguards.

One last thing, I added the linker option /OPT:NOWIN98 to minimize the binary size. This may cause a performance hit on non-NT systems (e.g., Win95, Win98, etc.). If you're using the DLL on such a system, I would recompile it without that linker option.



This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Vic Mackey
Web Developer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralQuran Database Pin
hinatiloos11-May-12 4:15
memberhinatiloos11-May-12 4:15 
GeneralMy vote of 5 Pin
manoj kumar choubey29-Mar-12 0:16
membermanoj kumar choubey29-Mar-12 0:16 
QuestionHow MD5 hash to save to Binary(16) Pin
zajo196928-Jun-11 0:04
memberzajo196928-Jun-11 0:04 
GeneralMy vote of 5 Pin
jjones778-Nov-10 11:20
memberjjones778-Nov-10 11:20 
GeneralMy vote of 5 Pin
Biswaranjan Das30-Jul-10 0:18
memberBiswaranjan Das30-Jul-10 0:18 
Generalweak MD5 Pin
Inktpatronen11-Jun-10 13:40
memberInktpatronen11-Jun-10 13:40 
GeneralFrom SQL2005 on there is no need for this external stored procedure. Pin
mbcooper7-Apr-10 8:24
membermbcooper7-Apr-10 8:24 
GeneralMD5 is a weak hashing function vulnerable to attacks Pin
Ali Hamdar12-Feb-10 11:59
memberAli Hamdar12-Feb-10 11:59 
GeneralMemory usage Pin
babreu41711-Aug-09 14:44
memberbabreu41711-Aug-09 14:44 
I know this is an older thread, but thanks for the article. I was able to create an extended stored procedure, but it seems that whenever I run it, sqlservr.exe keeps increasing in mem usage in task manager. Has anybody else seen this?
Generalsecurity issue Pin
DarekGr218-Jun-09 1:11
memberDarekGr218-Jun-09 1:11 
GeneralXP_MD5 for 64-bit SQL Server Pin
Vertisan25-Mar-09 4:59
memberVertisan25-Mar-09 4:59 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
AllDaylong7-Oct-09 12:48
memberAllDaylong7-Oct-09 12:48 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
mbcooper7-Apr-10 8:26
membermbcooper7-Apr-10 8:26 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
mbcooper7-Apr-10 9:07
membermbcooper7-Apr-10 9:07 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
bizcomit17-Sep-11 3:32
memberbizcomit17-Sep-11 3:32 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
tj5784-Dec-09 12:45
membertj5784-Dec-09 12:45 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
DeepaNarayanan19-Jan-10 11:18
memberDeepaNarayanan19-Jan-10 11:18 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
mbcooper7-Apr-10 6:28
membermbcooper7-Apr-10 6:28 
GeneralRe: XP_MD5 for 64-bit SQL Server Pin
narlasridhar5-Jul-10 2:30
membernarlasridhar5-Jul-10 2:30 
GeneralCan't get hash to match MD5CryptoServiceProvider.ComputeHash Pin
Neilius30-Jan-09 13:39
memberNeilius30-Jan-09 13:39 
GeneralRe: Can't get hash to match MD5CryptoServiceProvider.ComputeHash Pin
Smoak13-Apr-09 8:26
memberSmoak13-Apr-09 8:26 
GeneralRe: Can't get hash to match MD5CryptoServiceProvider.ComputeHash Pin
Neilius13-Apr-09 15:15
memberNeilius13-Apr-09 15:15 
GeneralCompiled Version of 64 bit Pin
the1gadget11-Nov-08 2:27
memberthe1gadget11-Nov-08 2:27 
QuestionLicenses/Right to Use Pin
familydude19-Sep-08 9:41
memberfamilydude19-Sep-08 9:41 
GeneralWell done! Pin
Jorge Bay Gondra22-Aug-08 4:37
memberJorge Bay Gondra22-Aug-08 4:37 
GeneralSQL Server 2000 - String functions Pin
pallavipatil8320-Sep-07 19:57
memberpallavipatil8320-Sep-07 19:57 
AnswerRe: SQL Server 2000 - String functions Pin
PawJershauge24-Oct-07 8:52
memberPawJershauge24-Oct-07 8:52 
GeneralWhy not use HashBytes Pin
PawJershauge12-Sep-07 15:18
memberPawJershauge12-Sep-07 15:18 
AnswerRe: Why not use HashBytes Pin
DejaVudew24-Oct-07 7:20
memberDejaVudew24-Oct-07 7:20 
GeneralRe: Why not use HashBytes Pin
PawJershauge24-Oct-07 8:43
memberPawJershauge24-Oct-07 8:43 
QuestionHow do I pass a SecureString var. to SQLS Stored Procedure? Pin
FredZimmerman30-Aug-07 5:07
memberFredZimmerman30-Aug-07 5:07 
Generalsrvparam_info Pin
moquai10-Mar-07 0:35
membermoquai10-Mar-07 0:35 
AnswerRe: srvparam_info Pin
moquai10-Mar-07 0:44
membermoquai10-Mar-07 0:44 
QuestionSQL 2005 Pin
phm373-Mar-07 10:37
memberphm373-Mar-07 10:37 
AnswerRe: SQL 2005 Pin
moquai10-Mar-07 1:45
membermoquai10-Mar-07 1:45 
GeneralRe: SQL 2005 Pin
jstring24-Apr-07 5:19
memberjstring24-Apr-07 5:19 
GeneralAbsolutely perfect Pin
jletual7-Nov-06 22:56
memberjletual7-Nov-06 22:56 
GeneralRe: Absolutely perfect Pin
amitguptadelhi29-Jan-07 23:23
memberamitguptadelhi29-Jan-07 23:23 
GeneralRFC 1321 produces 16 byte result not 32 byte Pin
TheReligion200024-Oct-06 1:34
memberTheReligion200024-Oct-06 1:34 
GeneralRe: RFC 1321 produces 16 byte result not 32 byte Pin
jletual7-Nov-06 23:01
memberjletual7-Nov-06 23:01 
GeneralI need script to convert one row of table to MD5 Pin
Pedro drow10-Jul-06 8:04
memberPedro drow10-Jul-06 8:04 
GeneralRe: I need script to convert one row of table to MD5 Pin
pixelbar24-May-07 0:44
memberpixelbar24-May-07 0:44 
Questionx64 version?? Pin
CRINET14-Mar-06 6:43
memberCRINET14-Mar-06 6:43 
AnswerRe: x64 version?? Pin
elgransan5-Apr-06 6:28
memberelgransan5-Apr-06 6:28 
AnswerRe: x64 version?? Pin
not_my_real_name6-Jun-06 16:25
membernot_my_real_name6-Jun-06 16:25 
GeneralRe: x64 version?? Pin
Igor Podsekin12-Oct-06 22:07
memberIgor Podsekin12-Oct-06 22:07 
GeneralRe: x64 version?? Pin
R Perez20-Mar-08 14:18
memberR Perez20-Mar-08 14:18 
GeneralC# Pin
govereem11-Jan-06 3:48
membergovereem11-Jan-06 3:48 
GeneralRe: C# Pin
Vic Mackey11-Jan-06 16:13
memberVic Mackey11-Jan-06 16:13 
GeneralExecution rights Pin
AlexEvans21-Sep-05 16:03
memberAlexEvans21-Sep-05 16:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171114.1 | Last Updated 8 Mar 2005
Article Copyright 2004 by Vic Mackey
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid