Troubleshooting Errors and Warnings on Query Expressions
Sometimes, SQL Server 2005 evaluates expressions in queries sooner than SQL Server 2000. This behavior provides the following important benefits:
- Indexes on computed columns can be matched to expressions in a query that are the same as the computed column expression.
- Redundant computation of the expression results is prevented.
However, depending on the nature of the query and the data in the database, run-time exceptions could occur in SQL Server 2005 if the query has an existing unsafe expression. These run-time exceptions include the following:
- Arithmetic exceptions: zero-divide, overflow, and underflow.
- Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number.
- Aggregation over a set of values that are not all guaranteed to be nonnull.
In SQL Server 2000, these exceptions may not occur in a particular application that uses specific data. However, a query plan that is changed because of changing statistics potentially could lead to an exception in SQL Server 2005. You can prevent these run-time exceptions by modifying the query to include conditional expressions such as NULLIF or CASE.
Important
Expressions that appear in a search condition, select list, or any other location within a query may be broken down and reorganized into one or more independent expressions. SQL Server may evaluate these independent expressions in any order with respect to each other. Filtering operations, including joins, are not necessarily applied before the result columns are computed.
In the following example, the expression x/y in the select list might be evaluated at any time, even for rows that ultimately do not qualify as output for the query.
USE tempdb
GO
IF OBJECT_ID('T','U') IS NOT NULL
DROP TABLE T
IF OBJECT_ID('S','U') IS NOT NULL
DROP TABLE S
GO
CREATE TABLE T(x float, y float, z nvarchar(30))
CREATE TABLE S(a float, b float)
GO
INSERT INTO T VALUES (1, 0, 'unknown')
INSERT INTO T VALUES(1, 2, '10')
GO
INSERT INTO S VALUES (1, 1)
INSERT INTO S VALUES (1, 2)
INSERT INTO S VALUES (1, 3)
INSERT INTO S VALUES (1, 4)
INSERT INTO S VALUES (1, 5)
The following query fails in SQL Server 2005 but finishes in SQL Server 2000.
SELECT x/y FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)
The query fails because the x/y expression causes a divide-by-zero error when the expression is evaluated for y=0.
The following code is a solution that lets the query execute correctly:
SELECT x/NULLIF(y,0) FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)
The expression NULLIF(y,0) returns NULL if y = 0. Otherwise, the expression returns the value for y. The expression x/NULL yields NULL and no exception occurs.
Consider the following example that involves converting character data to numeric types.
SELECT CONVERT(tinyint, z) FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)
The query fails with a conversion error when the query tries to convert the string 'unknown' to a tinyint. One way to solve this problem is to modify the query to perform the conversion only if z is numeric, by introducing a CASE statement such as:
SELECT CASE WHEN ISNUMERIC(z) = 1
THEN CONVERT(tinyint, z)
ELSE 0
END
FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)
A second solution is to forgo using the special string value 'unknown' in the database and use NULL instead. A third solution is to change the type of the z column to tinyint to avoid the conversion completely. Because solutions such as these require data and schema changes, respectively, applying these solutions may involve more work than modifying the query. However, you may want to consider these solutions if they also make other queries easier to write.
NULL Input Warning Issued by Aggregate Functions
Aggregate functions, such as MIN, issue a warning that a null value was eliminated if their input contains a NULL. This warning may be plan-dependent. If you do not want the NULL inputs to the aggregate to be processed and you do not want a warning issued, you can modify your query locally to eliminate null values. Consider the SELECT statement in the following example:
USE tempdb
GO
IF OBJECT_ID('newtitles','U') IS NOT NULL
....DROP TABLE newtitles
GO
CREATE TABLE dbo.newtitles
(title varchar (80) NULL ,
pubdate datetime NULL)
GO
INSERT dbo.newtitles VALUES('Title 1', NULL)
INSERT dbo.newtitles VALUES('Title 2', '20050311')
GO
SELECT t.title, t.pubdate, m.min_pubdate
FROM newtitles AS t,
(SELECT MIN(pubdate) AS min_pubdate
FROM newtitles) AS m
WHERE t.pubdate = m.min_pubdate
GO
In SQL Server 2005, this query produces a warning. To prevent the warning, change the query by adding the condition WHERE pubdate IS NOT NULL to filter out the null values before aggregation:
SELECT t.title, t.pubdate, m.min_pubdate
FROM newtitles AS t,
(SELECT MIN(pubdate) AS min_pubdate
FROM newtitles
WHERE pubdate IS NOT NULL) AS m
WHERE t.pubdate = m.min_pubdate
GO