For equality and nonequality comparison of an encrypted column to a constant value, Adaptive Server optimizes the column scan by encrypting the constant value once, rather than decrypting the encrypted column for each row of the table. The same restrictions listed in “Joins on encrypted columns” apply.
For example:
select sum(d.amount) from daily_xacts d where creditcard = '123-456-7890'
Adaptive Server cannot make use of an index to perform a range search on an encrypted column; it must decrypt each row before performing data comparisons. If a query contains other predicates, Adaptive Server selects the most efficient join order, which often leaves searches against encrypted columns until last, on the smallest data set.
If your query has more than one range search where there is no useful index, write the query so that the range search against the encrypted column is last. For example, the following query searches for Social Security Numbers of taxpayers in Rhode Island with incomes above $100,000. The range search of the zipcode column is positioned before the range search of the encrypted adjusted gross income column:
select ss_num from taxpayers where zipcode like ‘02%’ and agi_enc > 100000
