Depending on the version of SQL you are using, you can apply Transparent Data Encryption (introduced at 2008) which means that once you have provided the encryption key all operations continue as if the DB was not encrypted: http://technet.microsoft.com/en-us/library/bb934049.aspx
Be aware of a few things though:
1) The data is no longer encrypted once it leaves SQL server: so anyone monitoring the network can read it.
2) Backup data is not encrypted - so you need to secure that yourself. Backup files
are, but any data created via SELECT commands is not. So "SELECT * INTO NewTableName" will not automatically encrypt the new table, unless it is also in an encrypted database.
3) If you lose your encryption key, you will not be able to recover the data.