TSQL divide by zero encountered despite no columns containing 0

by

SQL is a declarative language; you write a query that logically describes the result you want, but it is up to the optimizer to produce a physical plan. This physical plan may not bear much relation to the written form of the query, because the optimizer does not simply reorder ‘steps’ derived from the textual form of the query, it can apply over 300 different transformations to find an efficient execution strategy.

The optimizer has considerable freedom to reorder expressions, joins, and other logical query constructions. This means that you cannot, in general, rely on any written query form to force one thing to be evaluated before another. In particular, the rewrite given by Lieven does not force the WHERE clause predicate to be evaluated before the expression. The optimizer may, depending on cost estimations, decide to evaluate the expression wherever it seems most efficient to do so. This may even mean, in some cases, that the expression is evaluated more than once.

The original question considered this possibility, but rejected it as ‘not making much sense’. Nevertheless, this is the way the product works – if SQL Server estimates that a join will reduce the set size enough to make it cheaper to compute the expression on the result of the join, it is free to do so.

The general rule is to never depend on a particular evaluation order to avoid things like overflow or divide-by-zero errors. In this example, one would employ a CASE statement to check for a zero divisor – an example of defensive programming.

The optimizer’s freedom to reorder things is a fundamental tenet of its design. You can find cases where it leads to counter-intuitive behaviours, but overall the benefits far outweigh the disadvantages.

Paul

More Related Contents:

  1. SQl CASE Statement
  2. How to calculate age in T-SQL with years, months, and days
  3. Convert multiple rows into one with comma as separator [duplicate]
  4. How to get sp_executesql result into a variable?
  5. How can I use optional parameters in a T-SQL stored procedure?
  6. T-SQL Decimal Division Accuracy
  7. Selecting a Record With MAX Value
  8. How to convert Seconds to HH:MM:SS using T-SQL
  9. T-SQL: Selecting rows to delete via joins
  10. Combining INSERT INTO and WITH/CTE
  11. What is a “batch”, and why is GO used?
  12. Getting the minimum of two values in SQL
  13. T-SQL loop over query results
  14. How to insert default values in SQL table?
  15. How to Round a Time in T-SQL
  16. T-SQL CASE Clause: How to specify WHEN NULL
  17. t-sql get all dates between 2 dates [duplicate]
  18. Return a value if no rows are found in Microsoft tSQL
  19. Check if a varchar is a number (TSQL)
  20. Using Aliases in Where Clause or an Alternative Option?
  21. Extract the first word of a string in a SQL Server query
  22. T-SQL – function with default parameters
  23. Search for one value in any column of any table inside a database
  24. Check if a varchar is a number (T-SQL)
  25. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  26. Convert unix epoch timestamp to TSQL datetime
  27. T-SQL Between Dates Confusion
  28. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  29. String.Format like functionality in T-SQL?
  30. How to parse string into date?

Leave a Comment