sql-serverperformance

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


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

Solution

  • 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)