In General, Real world environment of sql server will completely depend on certificates and signatures for encryption and decryption of confidential information like 'passwords' for the application end users.
I want to share you the basic concept of certificates.
first we need to create the master key for certificates, based on the key encryption going to be held.
---creating the master key
create master key encryption by password = 'admin123'
---Creating the certificate
create certificate [certificatename]
subject = 'sample_certificate'
---getting the certificate id
----encrypt the text by using the certificate
select encryptbycert('cert_id',N'sample text')
Hope u guys got the minimum basic idea about cerificates in sql server