Sql Server 2016 Always Encrypted - Comparison And Calculated Expression Using Always Encrypted Column In View

I have a column 'Amount' numeric(18,2) that I have made encrypted by using Encrypt Column wizard of SSMS v17. The column data is now encrypted. However, I have a view that uses som

Solution 1:

Comparison and mathematical operations are not allowed in encrypted columns. Currently the only operation possible on encrypted columns is equality. The answer by bastos would not work because SQL Server does not have the key.

You might have to implement this logic in the client application.

From official documentation

Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.