August 22, 2011 at 8:44 am
So I create a view with this SELECT:
SELECT TOP (100) PERCENT DBName, MetricDate, NumPages, OrigSize, CurSize, GrowthAmt
FROM dbo.DBA_DBGrowthRate
ORDER BY DBName
When run the view in DESIGN mode if gives me the correct and expected output, sorted on DBName. When I open the view I receive the output not sorted at all! The table has an identity keyfield (1,1), but I can't explain this...
Help?
Greetz,
Hans Brouwer
August 22, 2011 at 8:46 am
New "feature". Order bys are ignored in the views.
Nothing you can do except use order by from outside the view =>
Select Whatever From dbo.View ORDER BY Column
August 22, 2011 at 8:51 am
Ninja's_RGR'us (8/22/2011)
New "feature". Order bys are ignored in the views.Nothing you can do except use order by from outside the view =>
Select Whatever From dbo.View ORDER BY Column
Are you sure it's new? This is from BOL for 2005:
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 absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 22, 2011 at 8:58 am
If he never heard of it then it's new to him and new to 2k5 vs 2k ;-).
August 23, 2011 at 2:51 am
Tnx for answering all. I do find it peculiar, that in DESIGN mode the BY ORDER clause is working.
Oh well.
Greetz,
Hans Brouwer
August 23, 2011 at 8:39 am
FreeHansje (8/23/2011)
Tnx for answering all. I do find it peculiar, that in DESIGN mode the BY ORDER clause is working.Oh well.
It appears to work because in the design view you are not executing:
SELECT ... FROM dbo.MyView;
But rather, you are executing the code that makes up the view. That is another reason why you should move away from using the design view and rely on your own code.
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
August 23, 2011 at 8:49 am
In relational theory a relation (table, view etc) is an unordered set.
You should really alter your code so that the ORDER BY is on the outer most query.
If you are desperate for a short term fix you will probably be able to get away with something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE VIEW YourView
AS
SELECT TOP (<some very large number>) DBName, MetricDate, NumPages, OrigSize, CurSize, GrowthAmt
FROM dbo.DBA_DBGrowthRate
ORDER BY DBName
GO
August 26, 2011 at 2:51 am
So, the use of PERCENT is the problem? That's the only difference I notice...
Tnx for the heads-up all. I rarely use view design, but I was lazy this time...
Greetz,
Hans Brouwer
August 26, 2011 at 3:02 am
FreeHansje (8/26/2011)
So, the use of PERCENT is the problem? That's the only difference I notice...Tnx for the heads-up all. I rarely use view design, but I was lazy this time...
Not really.
SELECT TOP (99.999999) PERCENT would also force the ORDER BY.
But it's even more risky than the TOP(x) version to accidentially exclude some rows.
The best solution is not to use it when creating a view at all but rather do it when calling the view.
August 26, 2011 at 8:27 am
LutzM (8/26/2011)
FreeHansje (8/26/2011)
So, the use of PERCENT is the problem? That's the only difference I notice...Tnx for the heads-up all. I rarely use view design, but I was lazy this time...
Not really.
SELECT TOP (99.999999) PERCENT would also force the ORDER BY.
But it's even more risky than the TOP(x) version to accidentially exclude some rows.
The best solution is not to use it when creating a view at all but rather do it when calling the view.
In 2008 even that is not guaranteed. Having the TOP will allow you to create the view with the order by clause, but the only place it's guaranteed to be used is during the TOP determination. There still is no guarantee the return set is ordered. Per BOL:
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.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply