Posted by Erland Sommarskog on 05/28/05 01:00

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Using TSQL to encrypt in a UDF is a non-starter. It's always going to
> destroy performance because any non-trivial encryption algorithm is likely
> to be unfeasibly slow implemented in TSQL.

You could call an extended stored procedure from the UDF to perform
the actual encryption. Of course, it will still be slow since the UDF
and XP calls are expensive in themselves. Then again, Encryption
and high performance do not really go well together.

As for the problem posted, I would suggest that what is needed is:

SELECT SSN, FNAME, LNAME FROM USERS_VIEW
WHERE dbo.decrypt(LNAME_HASH) LIKE 'BON%'

Which is not going to perform well at all.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]