SHA256 Hash Stored Procedure for SQL Server 2008

Updated

The other day I was writing an ETL where I expected duplicate data to be accidentally sent over quite often so I needed an SHA-256 function to guarantee that a set of incoming rows were unique (notice I said the set of rows, not individual rows so that rules out using an index to prevent duplicates).  SQL Server 2012 has it but for this project I was stuck on SQL Server 2008 R2 which only has an SHA-1 function (obviously not good enough).

So here’s an SHA-256 stored procedure I wrote for SQL Server 2008…

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_SHA256( String strIn, out String strOut)
    {
        SqlPipe sp;
        sp = SqlContext.Pipe;

        ASCIIEncoding AE = new ASCIIEncoding();
        byte[] pBytes = AE.GetBytes(strIn);

        SHA256Managed hashvalue = new SHA256Managed();
        byte[] cHash = hashvalue.ComputeHash(pBytes);
        string hashedStr = System.Text.ASCIIEncoding .ASCII.GetString(cHash);
        strOut = hashedStr;
        sp.Send(hashedStr); 
    }
};

 

Leave a Reply

Your email address will not be published. Required fields are marked *