But the SQL Server 2005 optimiser is inconsistent

We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.

However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.

My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.

However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.

CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))

INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')

-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error

DROP TABLE #Problem

I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!

A view is nothing more than a representation of some SQL. The optimiser will not treat the view as a single entity but rather merge the SQL into the main SQL.

Secondly, SQL does not guarentee order of execution therefore you have to assume the worst. (as you've been told) This is core to how the opimser works.

You could create an indexed view and use the noexpand clause.

|||

This is not a bug. You can hit the problem in SQL Server 2000 also depending on your query plan and/or data. Some new changes to the query optimizer causes more chances of this happening in SQL Server 2005. See the older threads below for more information:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299697&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250271&SiteID=1

Note that even though the ANSI SQL standards talks about various parts of the SELECT statement getting evaluated in a specific order like ON, WHERE, GROUP BY, HAVING, SELECT, ORDER BY most relational database systems optimize the query as a whole for performance reasons. For example, the engine might reorder certain predicates based on internal processing logic or evaluate certain parts of the query using an indexed or materialized view or employ other join strategies. So you should not assume any order in the evaluation of predicates. The only way to guarantee it is to rewrite the predicate conditions in cases like this using a CASE expression or dump intermediate results into a temporary table and then perform the filters which may raise errors depending on the data. Hope this helps.

|||

I appreciate that you can never guarantee the order in which expressions get evaluated but surely these two commands should be optimised to the the same plan....they don't.

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

SQL Server 2000 optimiser was consistent in this regard.

|||

The optimiser is cost based. My understanding is that one of the major things that changes between versions is the costs of different operations due to changes in hardware etc.

I suspect that the different versions may have different costs and so different plans are compiled. I would also suspect to get different plans on different machines due to differences in processor memory etc.