'Filter based on decrypted value using DecryptByKey

I have the following simplified query which should filter the decrypted column values

SELECT ResourceId, ClientId, UserName
FROM dbo.Resources
WHERE DecrpytByKey(UserName) LIKE '%PETER%';

It doesn't work and returns me 0 results

If I use

SELECT  K.ResourceId, K.ClientId, K.DUserName
FROM (
    SELECT ResourceId, ClientId, UserName, DecrpytByKey(UserName) AS DUserName
    FROM dbo.Resources
) AS K 
WHERE K.DUserName LIKE '%PETER%'

This works correctly and gives me the desired results.

I want to go with something similar to option #1, since I want it to be efficient and filter the results before the join.

Is there a way to filter based on the decrypted value in the same select statement?

sql-servertsqlencryption


Solution 1:[1]

I'm not sure why it doesn't work, but your queries are doing exactly the same thing. Both of them are doing full scan on table so performance will be same.

Unfortunately, %% in this case are making performance fix impossible.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mkaczynski

Related Questions