Encrypt values of a column - SQL Server

We can use T-SQL EncryptByKey method to encrypt data in a specific column and DecryptByKey method to decrypt that. In this post I’ll explain how to use those methods.
As the first task we will create a symmetric key and a certificate as below.

USE Staff;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passwordabcd';
GO
CREATE CERTIFICATE Prod_Cert WITH SUBJECT = 'Key Protection';
GO

CREATE SYMMETRIC KEY My_Key WITH

    KEY_SOURCE = 'This is the symmetric key',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'This is the symmetric key'
    ENCRYPTION BY CERTIFICATE Prod_Cert;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE dbo.Employee
ADD EncryptedAge varbinary(128); 
GO

After that we can insert/update data to the column we just created while encrypting the data.

USE Staff;

GO

OPEN SYMMETRIC KEY My_Key
   DECRYPTION BY CERTIFICATE Prod_Cert;

UPDATE dbo.Employee
SET EncryptedAge
    = EncryptByKey(Key_GUID('My_Key'), 'my new value');
GO

Values in the column are encrypted as below.


Use below query to decrypt the value

OPEN SYMMETRIC KEY My_Key

   DECRYPTION BY CERTIFICATE Prod_Cert;
GO

SELECT Name, EncryptedAge 
AS 'Encrypted Age',
CONVERT(varchar, DecryptByKey(EncryptedAge)) 
AS 'Decrypted Age'
FROM dbo.Employee;

GO

You can get the decrypted value as below