September 26, 2011 at 8:56 am
How are Views stored in SQL Server? The obvious answer will be "they are saved queries," yet somehow this is not really the case. For example, you can use ORDER BY within a view to order the results IF you use a TOP keyword. The idea here is that the view will not necessarily return the results in this order, but it will use the ORDER BY to sort the results before returning the TOP #. So, if SQL Server is simply running a stored query, you would always expect to get ordered results, but this is not the case. Can anybody explain this or point me to an article that really gets into how SQL Server stores this data?
Thanks,
Jared
Jared
CE - Microsoft
September 26, 2011 at 9:03 am
jared-709193 (9/26/2011)
How are Views stored in SQL Server? The obvious answer will be "they are saved queries," yet somehow this is not really the case. For example, you can use ORDER BY within a view to order the results IF you use a TOP keyword. The idea here is that the view will not necessarily return the results in this order, but it will use the ORDER BY to sort the results before returning the TOP #. So, if SQL Server is simply running a stored query, you would always expect to get ordered results, but this is not the case. Can anybody explain this or point me to an article that really gets into how SQL Server stores this data?Thanks,
Jared
I'm not sure I understand your question.
Views are just "stored queries" as you stated in your question. What exactly makes you think that it's not so?
Can you post the TOP/ORDER BY example to clarify your question?
-- Gianluca Sartori
September 26, 2011 at 9:08 am
I guess I'm not clear on what you're asking.
A view is just a pre-written Select statement. It doesn't store any data except the query. When you query a view, it just takes the view definition and plugs that in where you put the view (usually the From clause), as if you had written the query that way.
So, if you have:
create view dbo.MyView
as
select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID;
And you use it this way:
select Col1
from dbo.MyView
where Col2 = 'apple';
Then it treats it as if you had written:
select Col1
from
(select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID) as MyView
where Col2 = 'apple';
Of course, materialized views (indexed views) are a little different, in that the Select statement and the indexed data are both stored, and the indexed data has to be updated if any of it changes in the underlying tables, but that's just a way to define a more complex index, the "View" itself is still just a stored Select statement. (There's more to indexed views, especially in Enterprise Edition, but the gist here is still accurate enough.)
Your question seems to indicate you understand that, in your statement that 'they are saved queries', but then seems to go off onto a bit of a tangent that I can't quite follow. Can you clarify your bit about Top and Order By and how that violates the above?
- 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
September 26, 2011 at 9:15 am
Here is a quote from BOL:
"A view cannot have an ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
Note: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself."
So, the idea is that the ORDER BY is only used to determine with TOP elements are returned. However, it does not guarantee that the returned results will be in that order. If it is strictly a saved query, it would return the results in the order specified.
I cannot give an exact example, but maybe this will help. We have a view on server A that is exactly the same as a view on server B. The query that the application uses against this view is SELECT columnA, columnB, ... , columnN FROM view. The view itself contains an order by clause, but the results from the 2 servers differed. If you strictly run the query contained in the view itself, the orders are the same. It is "as if" the view executes its saved query, but temporarily stores that result set in the same way it stores data in a table such that when you select from it without an ORDER BY you are not guaranteed an ordered result.
Thanks,
Jared
Jared
CE - Microsoft
September 26, 2011 at 9:19 am
jared-709193 (9/26/2011)
Can anybody explain this or point me to an article that really gets into how SQL Server stores this data?
It doesn't store it.
All that's saved for a view is the definition - the select statement. The data that the view returns is not stored anywhere (unless you have indexed the view). When you query a view, one of the first things that happens is that SQL replaces the name of the view with the saved definition and then executes the resultant query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2011 at 9:20 am
GSquared (9/26/2011)
I guess I'm not clear on what you're asking.A view is just a pre-written Select statement. It doesn't store any data except the query. When you query a view, it just takes the view definition and plugs that in where you put the view (usually the From clause), as if you had written the query that way.
So, if you have:
create view dbo.MyViewas
select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID;
And you use it this way:
select Col1from dbo.MyView
where Col2 = 'apple';
Then it treats it as if you had written:
select Col1from
(select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID) as MyView
where Col2 = 'apple';
Of course, materialized views (indexed views) are a little different, in that the Select statement and the indexed data are both stored, and the indexed data has to be updated if any of it changes in the underlying tables, but that's just a way to define a more complex index, the "View" itself is still just a stored Select statement. (There's more to indexed views, especially in Enterprise Edition, but the gist here is still accurate enough.)
Your question seems to indicate you understand that, in your statement that 'they are saved queries', but then seems to go off onto a bit of a tangent that I can't quite follow. Can you clarify your bit about Top and Order By and how that violates the above?
What I am saying is this, assuming my result set has less than 100000 rows:
create view dbo.MyView
as
select TOP 100000 Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID
ORDER BY Col1;
And you use it this way:
select Col1
from dbo.MyView
where Col2 = 'apple';
Is NOT the same as
select Col1
from
(select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID) as MyView
where Col2 = 'apple'
ORDER BY Col1;
Do you understand what I am saying a bit better now?
Thanks,
Jared
Jared
CE - Microsoft
September 26, 2011 at 9:20 am
jared-709193 (9/26/2011)
It is "as if" the view executes its saved query, but temporarily stores that result set in the same way it stores data in a table such that when you select from it without an ORDER BY you are not guaranteed an ordered result.
The result set is not temporarily stored anywhere. All SQL does is execute the query.
Note that without an order by you aren't guaranteed an ordered result no matter what, view or no view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2011 at 9:24 am
Maybe a better way for me to phrase the question is this: "Is the ORDER BY clause ignored in the ORDER of the result set of a view and only used to determine what is CONTAINED in the set?"
Jared
Jared
CE - Microsoft
September 26, 2011 at 9:26 am
jared-709193 (9/26/2011)
And you use it this way:
select Col1from dbo.MyView
where Col2 = 'apple';
Is NOT the same as
select Col1from
(select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID) as MyView
where Col2 = 'apple'
ORDER BY Col1;
Do you understand what I am saying a bit better now?
No, because those are not the same queries. Two different queries. SQL does not and cannot assume that there are fewer row than the TOP limits the data to.
The first query results in this:
select Col1
from (select TOP 100000 Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID
ORDER BY Col1) AS MyView
where Col2 = 'apple';
So the sort is used for the top. The filter is done after that and could change the order the rows return. But this has nothing to do with a view. That and the following are just not the same query
select Col1
from
(select Col1, Col2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID) as MyView
where Col2 = 'apple'
ORDER BY Col1;
The second guarantees that the rows will be returned ordered by Col1, the first (because the order by is not in the outer query) does not. Still has nothing to do with views.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2011 at 9:27 am
You are exactly right about the details, but you're drawing a conclusing that they don't support.
If you query the View without an Order By in the query calling the View, the order of the rows will be whatever SQL Server likes. This can vary for a variety of reasons, most often parallelism and sometimes cached data vs data-on-disk.
If the Top and Order By in the View can result in "ties", then two calls to the same View can even return different rows. For example, if you have "Top 10 ... Order By AccountID" in your View, and there are 20 rows with AccountID 1, then one query might return a completely different set of rows than another, from that View. Probably won't, because it'll probably pick the same 10 as being "the easiest" to get, but there are no guarantees on that.
It being a saved query has absolutely nothing to do with whether it will return the rows in a specified order or not.
The most common error in coding Views this way is "Top 100 Percent ... Order By ...", with the expectation that this will force the View to give results in a certain order. Actually, all SQL Server does in that case is ignore the Top and Order By, and run the query as if those didn't exist at all.
- 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
September 26, 2011 at 9:27 am
jared-709193 (9/26/2011)
Maybe a better way for me to phrase the question is this: "Is the ORDER BY clause ignored in the ORDER of the result set of a view and only used to determine what is CONTAINED in the set?"
Nothing whatsoever to do with views.
Order by only guarantees the order of the returned data when it is specified on the outer query. Not a subquery, not a view definition, not a function definition. The outer query, the final select.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2011 at 9:30 am
jared-709193 (9/26/2011)
Maybe a better way for me to phrase the question is this: "Is the ORDER BY clause ignored in the ORDER of the result set of a view and only used to determine what is CONTAINED in the set?"Jared
BINGO!!!
You nailed it exactly.
Edit: And Gail listed a few other things the same rule applies 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
September 26, 2011 at 9:35 am
So I think part of this "take home message" from both of you is that the following:
CREATE TABLE #TEMP (id INT, fruit VARCHAR(20))
INSERT INTO #TEMP
SELECT 3 AS id,'apple' AS fruit
UNION ALL
SELECT 2,'peach'
UNION ALL
SELECT 4,'orange'
UNION ALL
SELECT 1,'cherry'
SELECT *
FROM
(SELECT TOP 100 * FROM #TEMP ORDER BY id) a
MIGHT result in an ordered result set ascending in the id column, but is by no means GUARANTEED without the ORDER BY in the main query?
Thanks,
Jared
Jared
CE - Microsoft
September 26, 2011 at 9:38 am
I preffer to use won't (in my head) instead of might.
That way it lives no place for ignoring the need of the order by in the final step.
And since I usually deal with parallelism and 1M + rows datasets, it's so often the case anyways!
September 26, 2011 at 9:38 am
Yup. Absolutely.
On a tiny resultset like that, it probably will. Get larger and more complex and the bets are off.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply