I too have used XP_crypt.

Great tool for field level encryption. Its a good way to encrypt a field (which becomes a column) so that someone who gets the table can;t use it with out the right way to decrypt it.

But there in lies a trap. Unless the database engine itself knows how to work inside the encrypted data, you can't sort it or select by it without decrypting it first. Big performance hit overall.

my use? I use it to store passwords for a web application using one way encryption. I never search by them, I don't sort by them. I just encrypt my incoming value and test for equality. very little impact from that.