Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder SinghWhat's your question? I am not sure.
Have you checked the "CREATE VIEW" page in BOL, which states:
There are a few restrictions on the SELECT clauses in a view definition. A
CREATE VIEW statement cannot:
a.. Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder Singh|||There are issues with doing that.
Yes you can, but doing will force SQL Server to execute the entire view and
perform the order by before using the information. There are other better
ways to accomplish the same thing...
If your view really needs to be sorted, then you should create an index on
the view. As the first index must be clustered, the provides a sort order.
That aside, back to your workaround. By creating a view without
specifying a top and order by, SQL Server has a chance to optimise the
combined select and view query. By doing this it can filter the data before
applying any sort operations. Don't forget that a view (unless indexed)
does not contain any data, it's effectively a pre-prepared SQL statement.
Hope this helps a little.
Regards
Colin Dawson
www.cjdawson.com
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
> Hello All,
> BOL states that :-
> Before you create a view, consider these guidelines and one of the
> guideline is
> "The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword."
> But we can include Order By in A view if we use TOP like this
> create view v1 as
> select top 100 percent * from tablename order by columnName
> Any Thoughts
> With warm regards
> Jatinder Singh
>|||Hi
The guidelines might be for Updatable Views. However you can use Order
By clause in a view.
please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Some people do suggest using this "trick" to order views. I believe
that there are good reasons to avoid doing this.
This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of
defining the TOP x PERCENT so intuitively you would not expect it to
apply to the result of a SELECT from the view. 99% of the time it *may*
work but there is no guarantee that it will always continue to work.
A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:
CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT
NULL)
GO
CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y
GO
SELECT x FROM foo_view
The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimizer therefore has a choice either to
ignore the ORDER BY and retrieve an unsorted result set or to force a
sort which gives a sub-optimal execution plan. So TOP 100 PERCENT
either hurts performance, or doesn't work at all!
As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which
could break your code if it relies on an undefined feature.
In short, don't use undocumented tricks as a substitute for good design
and if you do use this feature be aware of its limitations and risks.
The "correct" and safe solution is simple:
SELECT ... FROM view ORDER BY ...
Why would you need to order the view itself?
Hope this helps.
David Portas
SQL Server MVP
--|||Hi All,
Thanks Naryan for your informative Answer
In Transact-SQL Reference (Location)
Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
In Creating and Maintaining Databases (Location)
The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword.
(The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.)
Try this
select * fom (select * from tablename order by columnname) AA
The point you mentioned appear even in this case Derived Table . My
Question was simple why there is inconsitency while mentioning
information in BOL. Now prehaps I made myself clear. Thanks again for
showing interest .
David
Excellent post !!
As always your answer give something new to learn .
"So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
As my assumption was that Query Optimizer will first expand the query
of view then it will rearrange the whole stuff and the execute it . My
assumption can be totally wrong .
I could not understand "doesn't work at all" part could you please
explain it further.
With Warm regards
Jatinder Singh|||"The optimizer therefore has a choice either to ignore the ORDER BY and
retrieve an unsorted result set [= TOP 100 doesn't work at all] or to force
a
sort which gives a sub-optimal execution plan [= hurts performance]". In
other words, both options are logically possible; neither is very
satisfactory.
David Portas
SQL Server MVP
--
"jsfromynr" wrote:
> Hi All,
> Thanks Naryan for your informative Answer
> In Transact-SQL Reference (Location)
> Include ORDER BY clause, unless there is also a TOP clause in the
> select list of the SELECT statement.
> In Creating and Maintaining Databases (Location)
> The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword.
> (The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.)
> Try this
> select * fom (select * from tablename order by columnname) AA
> The point you mentioned appear even in this case Derived Table . My
> Question was simple why there is inconsitency while mentioning
> information in BOL. Now prehaps I made myself clear. Thanks again for
> showing interest .
> David
> Excellent post !!
> As always your answer give something new to learn .
> "So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
> The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
> As my assumption was that Query Optimizer will first expand the query
> of view then it will rearrange the whole stuff and the execute it . My
> assumption can be totally wrong .
> I could not understand "doesn't work at all" part could you please
> explain it further.
> With Warm regards
> Jatinder Singh
>|||On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
(snip)
>If your view really needs to be sorted, then you should create an index on
>the view.
Hi Colin,
That won't work. Clustered indexes govern how data is stored on the
disk, not in which order it is processed. The optimizer might still pick
an unexpected plan. The order might even change between two consecutive
executions of the same query. See repro below for a proof that an
indexed view doesn't guarantee that the data is returned in order.
The only way to make sure you get the data in the order you need is to
provide an ORDER BY clause in the SELECT statement actually used to
retrieve the data.
create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
fname varchar(20) NOT NULL,
lname varchar(20) NOT NULL)
go
create view SortedBeatles with schemabinding
as
select fname, lname from dbo.Beatles
go
create unique clustered index BeatleIndex on SortedBeatles (fname)
go
insert into Beatles
values ('John', 'Lennon')
insert into Beatles
values ('Paul', 'McCartney')
insert into Beatles
values ('George', 'Harrison')
insert into Beatles
values ('Ringo', 'Starr')
go
select fname, lname from SortedBeatles
go
drop view SortedBeatles
go
drop table Beatles
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9kjff159m2i4qtb39a50hganommi87qgid@.
4ax.com...
> On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
> (snip)
> Hi Colin,
> That won't work. Clustered indexes govern how data is stored on the
> disk, not in which order it is processed. The optimizer might still pick
> an unexpected plan. The order might even change between two consecutive
> executions of the same query. See repro below for a proof that an
> indexed view doesn't guarantee that the data is returned in order.
> The only way to make sure you get the data in the order you need is to
> provide an ORDER BY clause in the SELECT statement actually used to
> retrieve the data.
> create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
> fname varchar(20) NOT NULL,
> lname varchar(20) NOT NULL)
> go
> create view SortedBeatles with schemabinding
> as
> select fname, lname from dbo.Beatles
> go
> create unique clustered index BeatleIndex on SortedBeatles (fname)
> go
> insert into Beatles
> values ('John', 'Lennon')
> insert into Beatles
> values ('Paul', 'McCartney')
> insert into Beatles
> values ('George', 'Harrison')
> insert into Beatles
> values ('Ringo', 'Starr')
> go
> select fname, lname from SortedBeatles
> go
> drop view SortedBeatles
> go
> drop table Beatles
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I had a feeling that it wasn't guarenteed. Your dead right that the only
way to guarentee the sort order is to use the Order by clause. Bad on me
for trying to cut a corner.
Regards
Colin Dawson
www.cjdawson.com
