Why 'No math function In Where Clause' in MS SQL?

Question

Most of SQL queries have no math operation on where clause.

What is wrong having them on 'where clause'? Is there any performance issue?

Eg:

SELECT * FROM Employee WHERE Salary*3 = 5000

Answer 1

If a where clause can use an index, it is often (but not always) faster. Using a math operation on a field will prevent the index from being used.

For example, if you had a table with a million rows and a date column that was indexed, query 1 here would by far outperform query 2 (they both retrieve all rows where the date is in the last 7 days):

query 1:

select date from table where date > dateadd(d, -7, getdate())

query 2:

select date from table where dateadd(d, 7, date) > getdate()

---

In your example, the query would be far better as:

select * from employee where salary = (5000 / 3)

Answer 2

Huh? No, nothing wrong at all with having math in a where clause. A where clause can contain any expression that uses column names, functions, or constants, as long the expression is a legal predicate.

ck gives the following two examples:

select date from table where date > dateadd(d, -7, getdate())

select date from table where dateadd(d, 7, date) > getdate()

and explains that the first is likely faster because it can use an index.

ck's correct, but it should also be noted that the first can be faster because dateadd(d, -7, getdate()) is a constant expression (it only needs to be evaluated once, no matter the number of rows) while dateadd(d, 7, date) in the second needs to be evaluated for each row.

But both of ck's examples are examples of "math" (expressions) being used in a where clause, which is what the OP is asking about.