A typical programmer may expect that the predicates are always evaluated in the order that they are specified, but this is not true in database systems.
For example, for the following clause,
where col11 = 5 and convert(int, col2) = 100
programmers may think col11 = 5 is always evaluated first, but this is not guaranteed. Query Optimizer may choose to first evaluate the second predicate, convert(int, col2) = 100. In fact, predicates may be pushed down the query tree, and not evaluated at the same time.
Such reordering is generally benign, as the row has to satisfy both conditions to qualify. But it may make a difference if the second predicate would cause an error if the first condition is not met. The following example demonstrates this.
Here we have a table with category and value both as string columns. From business logic, we know that when category is 'ID', value always contains a string with an integer ID. Therefore, we create a view, ID_View, that only shows the rows with category 'ID', and in the view, we will convert the value column to integer, and project as ID column.
-- Create tablecreate table dbo.test( id int not null primary key, category varchar(30) not null, value varchar(50) not null,name varchar(30)) go
-- Populate this table with data-- The logic is that when category is 'ID', value is a string of integer IDinsert into dbo.testvalues (1, 'Text', 'Hello world', 'foo')insert into dbo.testvalues (2, 'ID', '123', 'bar')go -- Create a view that only shows the rows that has category 'ID', -- and in that case, convert the value to an integer IDcreate view dbo.ID_view as select convert(int, value) ID, name from dbo.testwhere category = 'ID'go
-- this will show the rows where category is 'ID'select * from dbo.ID_viewgo
-- this will cause an errorselect * from dbo.ID_viewwhere ID = 123go
Selecting all rows from the view works perfectly fine. However, when we try to select from the view where ID is a certain value, 123, we get an runtime error:
Server: Msg 245, Level 16, State 1, Line 1Syntax error converting the varchar value 'Hello world' to a column of data type int.
The reason is that ID = 123 is expanded into convert(int, value) = 123, and it gets evaluated before the predicate category = 'ID'. Therefore, for the first row, with category as 'Text', and value as 'Hellow world', the conversion from value to int caused the runtime error.
In this particular example, there is no advantage of evaluating which predicate first, so Query Optimizer could choose either order. In other cases, it may be more efficient to evaluate the second predicate first, and Query Optimizer has no idea if the predicate evaluation would cause a runtime error, since it doesn't know the correlation between the two columns.
Note that the error happens on SQL 2000, but the query works fine on SQL 2005 because Query Optimizer happens to choose the other order. However, there is no guarantee that it won't change again in the future.
The correct way to implement such correlation is to use a CASE expression.
create view dbo.ID_view_new as select case when category = 'ID' then convert(int, value) else NULL end ID, name from dbo.testwhere category = 'ID'go-- this will works fineselect * from dbo.ID_view_newwhere ID = 123go
In the view definition, even though the filter indicates all rows will have category 'ID', in the select list, we still use the CASE expression to check again and return NULL otherwise. This can help prevent the error even with predicate reordering.
In a word, the order of evaluation for predicates is never guaranteed, so application logic should not depend on such order. If a predicate depends on other predicates and may cause runtime error if other conditions are not met, application should use CASE expression to make it work in any usage.