Below is a lightly edited copy of an answer that I posted on StackOverlow since link-only answers are generally discouraged:
You should look into using one of the several paired `ENCRYPTBY` / `DECRYPTBY` built-in functions:
For situations that do not require decryption (i.e. a one-way transformation that cannot be transformed back into the original value), such as how passwords are typically handled, you can use the HASHBYTES function, which can do the following algorithms:
SQL Server 2005 and newer can do:
SQL Server 2012 (and newer) added these:
All of the functions noted above (both `HASHBYTES` and `ENCRYPTBY_____` ) return a `VARBINARY`. If you need those hex digits in string format, the CONVERT function, starting in SQL Server 2008, added "style" numbers 1 and 2 to handle this:
;WITH cte AS ( SELECT HASHBYTES('SHA2_512', N'test') AS [HashedValue] ) SELECT cte.HashedValue, -- VARBINARY CONVERT(VARCHAR(200), cte.HashedValue) AS [DefaultOrStyle0], -- converted CONVERT(VARCHAR(200), cte.HashedValue, 1) AS [Style1], -- leading 0x CONVERT(VARCHAR(200), cte.HashedValue, 2) AS [Style2] -- no leading 0x FROM cte;
19 People are following this question.