I'm really glad to see this is being received well. The code snippets got reformatted a little, so I apologize if hard to read; this is my first article & I have lot to learn about publishing in HTML.
We just reviewed this with our development team and they made a great suggestion which we've adopted as our standard approach, and that I wish I'd included in the original article: write custom "encrypt" and "decrpyt" UDFs for each encrypted column. These make subsequent coding much simpler, developers don't have to know names of the symmetric key or the certificate, don't have to worry about casting the datatypes, etc.
create function dbo.fnEncryptAccountNbr(@AccountNbr varchar(16))
return EncryptByKey(Key_GUID('MyKey'), @AccountNbr);
create function dbo.fnDecryptAccountNbr(@EncryptedAccountNbr varbinary(68))
return convert(varchar(16), DecryptByKeyAutoCert(cert_id('MyCert'), null, @EncryptedAccountNbr));
I have only tested this a little bit, but so far don't see any performance hit caused by these UDFs (I've been badly burned by UDF performance--or lack thereof--in SQL2000, am always on guard for that now).