Home Forums SQL Server 2005 T-SQL (SS2K5) Division by zero on a SQL Instance while a different Instance run correctly RE: Division by zero on a SQL Instance while a different Instance run correctly

  • A possible reason for this error is that the division expressions are being evaluated for some rows before those rows are excluded by the WHERE clause. The same problem is often observed when CASTing datatypes, and using a WHERE clause to filter only those rows where the CAST would succeed.

    This can happen because the optimiser is free to evaluate your SELECT expressions either before or after filtering by the WHERE clause. Even if the data in your two databases is the same, other factors (indexing, server resources...) may affect the optimser.

    Your solution of rewriting the expression is probably the best way, since it is tricky (impossible??) to be sure what the optimiser will do.

    More info:

    T-SQL functions do no imply a certain order of execution[/url]

    On SQL Server boolean operator short-circuit[/url]