There's a lot to this.
First, encryption. I'd do the encryption on the data load. If not, then potentially you are storing data that compromises your security. You can create the keys in the load, or in the db, but essentially you will do this:
1. Create a certificate
2. Back up the certificate - DO THIS FIRST
3. create a symmetric key, encryted by the certificate
4. assign rights.
In the load, you can then open the symmetric key and use encryptbykey()
function to encrypt the data and store it as varbinary. I assume the precision will be captured correctly, but that's something I haven't messed with.
It really depends on what you're doing here. In terms of a proc, you can do calculations and decryption inline. The decryptbykey function will do this, though it takes CPU. I don't know what calculations you're performing, but at any scale, this will slow down the server. How much depends on the volume of data and level of encryption algorithm you use. Longer is stronger, but more sources.
A couple things. When you get a result from decryptbykey(), you need to cast back to the original data type. If not, you won't be able to read the data. If you get null, then either you've cast incorrectly, or the sym key isn't open.
Note that your sym key code becomes important, because the same sym key code will give you the same key. That's potentially a place where your security is compromised.
Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com