Fast search on encrypted data

The search operation on SQL Server database may initiate a full table scan which results in slow search process. To avoid full table scan in SQL Server database, the user can bypass the trigger and view architecture and go directly to the encrypted table. This can be done by encrypting the search string and specifying the encrypted value in WHERE clause.

Steps

To implement faster search:

• the SQL syntax for the original query must be modified in the application or in the stored procedure depending on where the query is performed.

• A new index also needs to be created on the new encrypted column.

Example

select first, last, ingrian.dbo.ing_d_vrc_nm420
(CCNUM_NEW, 'Training', 'dbo', 'customer', 'CCNUM',
0xB9B20B6C75593DDDE385023583E75BB4)
from training.dbo.CUSTOMER_NEW
where CCNUM_NEW = (select ingrian.dbo.ing_e_vrc_nm420
('1234567890000001', 'Training', 'dbo', 'customer', 'CCNUM',
0xB9B20B6C75593DDDE385023583E75BB4))

This query is capable of executing in mere milliseconds instead of seconds or minutes, required to perform the non-optimized query.