February 25, 2009 at 1:31 pm
Guys,
I have a strange issue here. We migrated a db from SQL 2000 to SQL 2005. The compatibility mode is set to 90 on SQL 2005. There is a very old application which is using this db. We cannot change the code as we don't have tools do it. Here is the problem: There is a view which is queried by this app as -
select * from myview where col11 = 'Yes' and col12 = 'No'
The view looks like this:
create view myview
as
select top 1000 col1, col2, col3
from table1 t1 join table2 t2 on t1.col4 = t2.col4
where t2.col4 = 'some value'
order by col1
go
Everything works as expected in SQL 2000. For some strange reason, the same view and query return only part of result set on SQL 2005. If I run this query using view as :
select * from myview where col11 = 'Yes' and col12 = 'No'
The above returns 78 rows.
If I run the query below:
select col1, col2, col3
from table1 t1 join table2 t2 on t1.col4 = t2.col4
where t2.col4 = 'some value' and t1.col1 = 'Yes' and t1.col2 = 'No'
order by col1
It returns 87 rows. This is very very strange!
What is even stranger is - I change the view as:
alter view myview
as
select top 10000 col1, col2, col3
from table1 t1 join table2 t2 on t1.col4 = t2.col4
where t2.col4 = 'some value'
order by col1
go
Notice that I changed the number of rows to 10000 from 1000 The above returns all 87 rows. The result set must have 87 rows only.
Any ideas?
Thanks.
February 25, 2009 at 1:45 pm
I would tend to say there are only 78 rows that satisfy the Where clause outside the view that are in the criteria within the view.
First you ask for the first 1000 rows, then within those thousand rows, you want the ones that have No and Yes in the requested columns. You get 78 rows.
The second query checks for the No and Yes first, then limits the number of rows.
The third query finds 87 in the the much-expanded set of rows in the modified view.
It makes total sense. It's the sequence of criteria.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 2:43 pm
Watch out for SQL 2005 has issues with ORDER BY in the definition of the views!
* Noel
February 25, 2009 at 2:59 pm
noeld (2/25/2009)
Watch out for SQL 2005 has issues with ORDER BY in the definition of the views!
Not really. It does what it's supposed to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 3:23 pm
noeld (2/25/2009)
Watch out for SQL 2005 has issues with ORDER BY in the definition of the views!
No, the only time it is not valid is when you use TOP 100 PERCENT in the view and expect the order of the rows returned to be honored. SQL Server 2005 and above does not guarantee the order of rows returned from a view unless the outer query specifies the order.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 25, 2009 at 4:20 pm
Thanks for the replies you guys.
Let me add more here: The application code relies on the order by clause in the view to sort the results. So I cannot eliminate it from the view. After reading about limitation of order by clause in the view in SQL 2005 I came up with this workaround where I cheat by saying I want to see TOP 1000 rows even though I know there won't be more than 100 rows in the result so I could use ORDER BY. Makes sense? It worked for another view I had. That view has been working fine for two months now.
This one is what is driving me crazy.
GSquared,
I gave the independent query and a query with view to show that there are actually 87 rows that satisfy the criteria. However if I use TOP 1000 in the view it returns only 78 rows. To see all 87 rows I had to use TOP 10000. Hope that is clear. Regarding the sequence of filtering criteria I would disagree with you. Remember that SQL does JOIN first followed by WHERE and ORDER BY and then TOP. So no matter how I arrange them the result should be same.
February 25, 2009 at 4:39 pm
Jeffrey Williams (2/25/2009)
noeld (2/25/2009)
Watch out for SQL 2005 has issues with ORDER BY in the definition of the views!No, the only time it is not valid is when you use TOP 100 PERCENT in the view and expect the order of the rows returned to be honored. SQL Server 2005 and above does not guarantee the order of rows returned from a view unless the outer query specifies the order.
Beg to differ:
From BOL
When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
The order is NOT honored in ANY case!
* Noel
February 26, 2009 at 7:41 am
Venu Dukka (2/25/2009)
Thanks for the replies you guys.Let me add more here: The application code relies on the order by clause in the view to sort the results. So I cannot eliminate it from the view. After reading about limitation of order by clause in the view in SQL 2005 I came up with this workaround where I cheat by saying I want to see TOP 1000 rows even though I know there won't be more than 100 rows in the result so I could use ORDER BY. Makes sense? It worked for another view I had. That view has been working fine for two months now.
This one is what is driving me crazy.
GSquared,
I gave the independent query and a query with view to show that there are actually 87 rows that satisfy the criteria. However if I use TOP 1000 in the view it returns only 78 rows. To see all 87 rows I had to use TOP 10000. Hope that is clear. Regarding the sequence of filtering criteria I would disagree with you. Remember that SQL does JOIN first followed by WHERE and ORDER BY and then TOP. So no matter how I arrange them the result should be same.
No. The view limits it to 1000 rows, which preserves only 78 of the rows you want. The remaining rows are beyond the 1000 rows mark. Then, when you query the view, you only get the rows in that Where clause.
Sequence of actions within a single query has NOTHING to do with it.
Here's how to prove this. Query the whole view. Everything in it. Dump that into a temp table. Then query the temp table with your Where clause for your final query. You'll only get 78 rows. No matter how you query the temp table, there are only 78 rows in it that will satisfy the final Where clause. That's essentially what you're doing when you do it your way.
Using an Order By in the view but not using one in the final query means you may or may not get the sequence you want. You might, but you can't guarantee it.
All you're doing with your "other view where that worked" is getting lucky. Generally, I don't like to rely on luck in my code, I prefer to rely on engineering principles and the laws of the system.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply