SQL Server Encrypt Column data using Symmetric Key

USE TEACHMESQLSERVER
GO

-- STEP 1 CREATING A DUMMY TABLE

CREATE TABLE EMP(EMPID VARCHAR(12))
GO

-- STEP 2 INSERTING DUMMY ROW

INSERT INTO EMP VALUES('111-111-111')
GO

-- STEP 3 CREATING MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='ABCD1234'
GO

-- STEP 4 CREATING CERTIFICATE

CREATE CERTIFICATE CERTIFICATE1 WITH SUBJECT ='PROTECT DATA'
GO

-- STEP 5 CREATING SYMMETRIC KEY

CREATE SYMMETRIC KEY SYMMETRICKEY WITH ALGORITHM =AES_128
ENCRYPTION BY CERTIFICATE CERTIFICATE1
GO

-- STEP 6 CREATING A NEW COLUMN WITH DATA TYPE VARBINARY

ALTER TABLE EMP ADD ENCRYPTEDEMPID VARBINARY(MAX)
GO

-- STEP 7 ENCRYPT THE COLUMN DATA USING UPDATE COMMAND

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
GO
UPDATE EMP SET ENCRYPTEDEMPID = ENCRYPTBYKEY(kEY_GUID('SYMMETRICKEY'),EMPID) FROM EMP
GO
SELECT * FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY


-- STEP 8 DECRYPT AND READ THE COLUMN DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
SELECT CAST(DECRYPTBYKEY(ENCRYPTEDEMPID) AS VARCHAR(MAX)) AS DECRYPTED FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY

-- STEP 9 INSERT THE ENCRYPTED  DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
INSERT INTO EMP(ENCRYPTEDEMPID) VALUES(ENCRYPTBYKEY(KEY_GUID('SYMMETRICKEY'),CAST('121-345-765' AS varchar)))
CLOSE SYMMETRIC KEY SYMMETRICKEY
SELECT * FROM EMP
GO

Get detailed explanation here.