MS SQL: MD5 Hash

The non-reversable hashing algorithm MD5 is supported in Microsoft SQL Server, but is not directly accessable though a simple transact-SQL function. Normally you would store the MD5 hash value as a hexadecimal string in your database. The HashBytes function returns a binary array of hash data. To convert the binary data to hex we need to use the function fn_varbintohexstr.


To make your code easier to read, I've made a wrapper in a custom userdefined function:

CREATE FUNCTION [dbo].[sudf_Common_Md5Hash]

(

      -- Add the parameters for the function here

      @strValue nvarchar(max)

)

RETURNS nvarchar(32)

AS

BEGIN

      -- Declare the return variable here

      declare @strResult nvarchar(32)

      -- Generate the MD5

      set @strResult = SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @strValue)), 3, 32)

     

      -- Return the result of the function

      RETURN @strResult

END

MS SQL: Atomic Insert or Update (UPSERT)

It is not straight forward to create an atomic insert or update statement in SQL Server, i.e. we update the row if it exists. Otherwise we insert a new row.

The following statement (notice the locks) will create a more or less atomic statement:


-- Start transaction
begin tran


-- Row Exists?
if not exists (select * from <table> with (updlock, rowlock, holdlock) where <PK = ...>    
begin
        <insert>
end
else
begin
        <update>
end

-- End Transaction
commit