Execute the following Microsoft SQL Server Transact-SQL script in Management Studio Query Editor to demonstrate the use of varbinary(max) in data encryption.

USE AdventureWorks

-- SQL Server varbinary(max) - varbinary max

-- SQL Server encryption - SQL Server decryption

CREATE CERTIFICATE CertificateZ

ENCRYPTION BY PASSWORD = '007$$$A'

WITH SUBJECT ='CertificateZ',

START_DATE = '2005/01/01',

EXPIRY_DATE = '2010/01/01'

DECLARE  @CypherText VARBINARY(MAX)

SET @CypherText = EncryptByCert(Cert_ID('CertificateZ'),'SQLUSA')

SELECT Cypher = @CypherText,

       Decyphered = convert(VARCHAR,DecryptByCert(Cert_ID('CertificateZ'),

@CypherText,N'007$$$A'))

/* Results

Cypher      Decyphered

0xE5E047D77789F08603FD487C0C8AECEA65996540552980AD48D2B2C9C04093C3964B967A

FBD3AB60A13688F8E4E7AB1EF0EE6A9243C597CED181FC1AC2DAA6FA976DEF11453BEE02BE

FAC6E49F60396165DA86A55BDA4AF5A77918818EF10EFB5F64EED358EF6DEBD2438E778677

097DF2C8CFE1D4837EAB4124F66ED966AF75      SQLUSA

*/