I often recommended to only encrypt data in SQL Server using symmetric keys and to reserve the use of asymmetric encryption for protection of symmetric keys and for signing. In this post, I will go in more detail about why asymmetric encryption is not appropriate for protecting data.

There are three reasons why asymmetric encryption is not suitable for encrypting data:

(1) Asymmetric encryption is much slower than symmetric encryption.

(2) For small data like SSNs and CCNs, asymmetric encryption will result in a blob that will be significantly larger than what you would get from symmetric encryption - about 2 times larger or more.

(3) While for symmetric encryption, the largest data that can be compressed is around 8000 bytes, because of limitations in the SQL Server encryption interfaces, for asymmetric key encryption, the limit is much smaller and is due to limitations of the technology itself. The limits are: a 512 bit RSA key can encrypt up to 53 bytes, a 1024 bit key can encrypt up to 117 bytes, and a 2048 bit key can encrypt up to 245 bytes (reminder: in SQL Server, both certificates and asymmetric keys are wrappers over RSA keys).

So, by using asymmetric encryption to encrypt data, you pay additional time and space costs, and on top of that you are limited about how large a piece of data you can encrypt.

All these points are unrelated to the actual implementation of the algorithms; they are simply derived from the properties of RSA encryption, which is the only form of asymmetric encryption supported in SQL Server 2005.

Related to (3), also see this post and this post, which contain information that can help you determine the limits for RSA keys of different sizes than I enumerated above (or you can just determine those limits by trial and error).

Also related is this post about the use of certificates in SQL Server and this post about the difference between certificates and asymmetric keys.