VIEW's ORDER BY columns necessary in column list

microsoft.public.sqlserver.programming

14 Jul 2006 12:40 PM

Keith G Hicks

I recently created a view (based on another view) that has an ORDER BY in it
that is requiring that the ORDER BY columns be listed in the column list
after the view name. This is the first time I've encountered this and am
wondering why they are required to be in the list even though they do not
appear in the SELECT part of the view. I am not permitted to post the code
here, but here's a similar example:

View 1:

CREATE VIEW dbo.CustView1 (CustName, CustType, CustTable, CustActive,
Designation1, Designation2, Designation3 ) AS
SELECT CustName, CustType, 'A' AS CustTable, CAST(0 AS BIT) AS CustActive,
CustID AS Designation1, 0 AS Designation2, 0 AS Designation3 FROM Custs1
WHERE CustType <> 'X'
UNION ALL
SELECT CustName, CustType, 'B' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
AS Designation1, CustID AS Designation2, 0 AS Designation3 FROM Custs2
WHERE CustType <> 'X'
UNION ALL
SELECT CustName, CustType, 'C' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
AS Designation1, 0 AS Designation2, CustID AS Designation3 FROM Custs3
WHERE CustType <> 'X'

View 2:

CREATE VIEW dbo.CustView2 (CustName, CustType, CustTable, CustActive,
IsLvl1, OtherCustInfo, Designation1, Designation2, Designation3) AS
SELECT TOP 100 PERCENT CustName, CustType, CustTable, CustActive, CASE WHEN
CustType = 'A' THEN CAST(1 AS BIT) AS IsLvl1, dbo.ConcatCustName(CustName,
CustType, CustActive) AS OtherCustInfo FROM dbo.CustView1 ORDER BY
Designation1, Designation2, Designation3

In the actual 2nd view I also use LTRIM, RTRIM, NULLIF, ISNULL and COALESCE
if that makes any difference.

Why are Designation1, Designation2, Designation3 required in the column list
of the 2nd view?

I'm not finding the answer in BOL. -->

column

Is the name to be used for a column in a view. Naming a column in CREATE
VIEW is necessary only when a column is derived from an arithmetic
expression, a function, or a constant, when two or more columns may
otherwise have the same name (usually because of a join), or when a column
in a view is given a name different from that of the column from which
derived. Column names can also be assigned in the SELECT statement.

If column is not specified, the view columns acquire the same names as the
columns in the SELECT statement.

Thanks,

Keith

14 Jul 2006 1:27 PM

What do you expect from the ORDER BY clause in a view? Life without it is far
better - sort the rows at retrieval, as you're supposed to do.

Views (except for indexed ciews) are slow; even more so if the data needs to
be sorted. And in the end SQL Server does *not* guarantee the rows will be in
the designated order, unless ORDER BY is also declared when the data is
retrieved.

ML

---
http://milambda.blogspot.com/

14 Jul 2006 1:57 PM

David Portas

ML wrote:

> Views (except for indexed ciews) are slow

Views are just a method of aliasing a query. They don't actually DO
anything so they can't possibly be either slow or quick.

I expect you mean that a view could cause some query to run slower
compared to some other query that doesn't use a view. If that is what
you meant then as a general rule I'd say you are wrong. In many if not
most cases you won't find any measurable performance difference between
a query referencing a view and an equivalent query that doesn't. For
sure there are exceptions but as a general rule I wouldn't say that
views have a significant detrimental impact on performance - provided
you don't do silly things like trying to use ORDER BY in a view.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

14 Jul 2006 2:06 PM

Of course. You're right. Insufficient wording on my part. Thank you for
pointing that out.

ML

---
http://milambda.blogspot.com/

14 Jul 2006 2:17 PM

Aaron Bertrand [SQL Server MVP]

> I expect you mean that a view could cause some query to run slower
> compared to some other query that doesn't use a view. If that is what
> you meant then as a general rule I'd say you are wrong.

My *guess* is that views are perceived to be slower because people often
create a view like this:

CREATE VIEW dbo.Gross
AS
    SELECT
        AuthorID,
        AuthorName,
        BookID,
        BookTitle,
        OrderID,
        OrderDate,
        PublisherName,
        CustomerID,
        CustomerName,
        CustomersMothersMaidenName
    FROM
        // picture INNER JOINs here, not commas
        Authors, Books, Orders, Publishers, Customers, etc etc;
GO

And then use that view to only get a list of publishers.

In general, I agree, a view is not used to boost performance, nor will it
necessarily degrade performance unless abused like above. The biggest
advantages of a non-indexed view is ease of programming (write the join(s)
once, not repeating them in all queries that actually need the join(s)), and
obscuring schema.

A

14 Jul 2006 3:12 PM

I was referring to how the execution of a view with TOP/ORDER BY is slowed
down while the ordering of the result set nonetheless remains unpredictable
unless an additional ORDER BY is specified at data retrieval.

ML

---
http://milambda.blogspot.com/

14 Jul 2006 3:35 PM

Aaron Bertrand [SQL Server MVP]

Oh, I see. So you meant to say,

"Views with TOP/ORDER BY are slower than those without"?

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:1321791B-D609-4EDB-9544-554BAAAA2900@microsoft.com...
>I was referring to how the execution of a view with TOP/ORDER BY is slowed
> down while the ordering of the result set nonetheless remains
> unpredictable
> unless an additional ORDER BY is specified at data retrieval.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

14 Jul 2006 4:01 PM

Yes, but the point being that all the efforts used to sort data (in the view)
are in vain.

In SQL 2005 TOP 100 PERCENT...ORDER BY is ignored, but in previous versions
it remains a part of the execution plan, doen't it?

Still, the sorting in the result set is not guaranteed simply by the fact
that sorting is specified in the view's definition, right?

ML

---
http://milambda.blogspot.com/

14 Jul 2006 4:08 PM

Aaron Bertrand [SQL Server MVP]

> Yes, but the point being that all the efforts used to sort data (in the
> view)
> are in vain.

True.

However, I don't yet agree that on its own, adding the (we all know,
useless) TOP 100 PERCENT / ORDER BY to a view will slow it down. It can,
but it is not guaranteed. For example, if the ORDER BY happens to coincide
with the clustered index(es) on the table(s), the portion of the plan that
works on the sort shouldn't change at all (assuming you use ORDER BY when
querying against the view).

> Still, the sorting in the result set is not guaranteed simply by the fact
> that sorting is specified in the view's definition, right?

Correct. But analyzing sort order is not the same thing as measuring
performance! It is not right to say a view is slower than a query, or that
a view with TOP / ORDER BY is slower than one without. I will agree 100%
that TOP / ORDER BY is completely pointless, and is mostly a holdover from
the days when the optimizer really did tend to return the results in that
order. While it has never been guaranteed, it worked pretty consistently
before all of the engine changes in SQL Server 2005, and a lot of people
voiced complaints that the uncodumented and non-guaranteed behavior they
were relying on stopped working.

A

14 Jul 2006 4:31 PM

I agree with you all the way. The actual fact whether ORDER BY impedes the
execution or not depends on many factors, which I failed to mention in my
post to Keith. Generalizing was , is and will be inappropriate, to say the
least.

If in a particular case TOP/ORDER does not affect the execution of the query
in a negative way a good reason not to use it still remains in the fact that
there is no actual benefit in using it.

> While it has never been guaranteed, it worked pretty consistently
> before all of the engine changes in SQL Server 2005, and a lot of people
> voiced complaints that the uncodumented and non-guaranteed behavior they
> were relying on stopped working.

Coming from a legal background the expression "not guaranteed" has always
sounded convincing enough for me not to use the features designated by it. :)

Any claims by those users have no solid ground, unless they suggest adding a
"DEFAULT_ORDER_BY" clause to the definitions of views and tables. :)
While this sounds like something a vendor might do it has absolutely nothing
to do with what SQL is about.

ML

---
http://milambda.blogspot.com/

14 Jul 2006 3:58 PM

Tracy McKibben

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

>> I expect you mean that a view could cause some query to run slower
>> compared to some other query that doesn't use a view. If that is what
>> you meant then as a general rule I'd say you are wrong.
>
> My *guess* is that views are perceived to be slower because people often
> create a view like this:
>
> CREATE VIEW dbo.Gross
> AS
>     SELECT
>         AuthorID,
>         AuthorName,
>         BookID,
>         BookTitle,
>         OrderID,
>         OrderDate,
>         PublisherName,
>         CustomerID,
>         CustomerName,
>         CustomersMothersMaidenName
>     FROM
>         // picture INNER JOINs here, not commas
>         Authors, Books, Orders, Publishers, Customers, etc etc;
> GO
>
> And then use that view to only get a list of publishers.
>
> In general, I agree, a view is not used to boost performance, nor will it
> necessarily degrade performance unless abused like above. The biggest
> advantages of a non-indexed view is ease of programming (write the join(s)
> once, not repeating them in all queries that actually need the join(s)), and
> obscuring schema.
>
> A
>
>
>

Or the ever popular:

CREATE VIEW View1
AS
SELECT *
FROM Table1_Current
INNER JOIN Table2_Current
ON Table1_Current.RowID = Table2_Current.HeaderRowID

CREATE VIEW View2
AS
SELECT *
FROM Table1_History
INNER JOIN Table2_History
ON Table1_History.RowID = Table2_History.HeaderRowID

CREATE VIEW View3
AS
SELECT * FROM View1
UNION
SELECT * FROM View2

Repeat ad nauseum... Real-life code from an application running at my
current employer. Table names have been changed to protect the
innocent. Optimizing this crap is like peeling an onion, each layer you
peel off just brings more tears...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

14 Jul 2006 4:06 PM

> Table names have been changed to protect the innocent.

Table names (=tables) may belong to the innocent, but the same cannot be
said for the views.

ML

---
http://milambda.blogspot.com/

14 Jul 2006 1:30 PM

Alejandro Mesa

Keith G Hicks,

1 - What is the meaning of using "order by" clause inside a view, if SQL
Server will not guaranteed that the result of selecting from the view will be
ordered if you do not use the "order by" clause in the "select" statement
referencing the view.

Example:

use northwind
go

create view dbo.v1
as
select orderid, customerid, getdate() as orderdate
from dbo.orders
go

create view dbo.v2
as
select top 100 percent orderid, customerid
from dbo.v1
order by orderdate
go

set showplan_text on
go

select *
from dbo.v2
go

set showplan_text on
go

drop view dbo.v2, dbo.v1
go

Result:

|--Sort(ORDER BY:([Expr1003] ASC))
|--Compute Scalar(DEFINE:([Expr1003]=getdate()))
|--Index Scan(OBJECT:([northwind].[dbo].[Orders].[CustomerID]))

Notice that the result will be ordered by "CustomerID" and not by [orderdate].

2 - Are you using "select distinct" in the definition of the second view?

Is that is the case, then take a look to the "create view" statement in BOL.

Example reproducing the error:

use northwind
go

create view dbo.v1
as
select 1 as c1, 0 as c2
union all
select 2, 1
go

create view dbo.v2
as
select distinct top 100 percent c1
from dbo.v1
order by c2
go

select *
from dbo.v2
go

drop view dbo.v2, dbo.v1
go

Result:

Server: Msg 145, Level 15, State 1, Procedure v2, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

AMB

Show quote

"Keith G Hicks" wrote:

> I recently created a view (based on another view) that has an ORDER BY in it
> that is requiring that the ORDER BY columns be listed in the column list
> after the view name. This is the first time I've encountered this and am
> wondering why they are required to be in the list even though they do not
> appear in the SELECT part of the view. I am not permitted to post the code
> here, but here's a similar example:
>
> View 1:
>
> CREATE VIEW dbo.CustView1 (CustName, CustType, CustTable, CustActive,
> Designation1, Designation2, Designation3 ) AS
> SELECT CustName, CustType, 'A' AS CustTable, CAST(0 AS BIT) AS CustActive,
> CustID AS Designation1, 0 AS Designation2, 0 AS Designation3 FROM Custs1
> WHERE CustType <> 'X'
> UNION ALL
> SELECT CustName, CustType, 'B' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
> AS Designation1, CustID AS Designation2, 0 AS Designation3 FROM Custs2
> WHERE CustType <> 'X'
> UNION ALL
> SELECT CustName, CustType, 'C' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
> AS Designation1, 0 AS Designation2, CustID AS Designation3 FROM Custs3
> WHERE CustType <> 'X'
>
> View 2:
>
> CREATE VIEW dbo.CustView2 (CustName, CustType, CustTable, CustActive,
> IsLvl1, OtherCustInfo, Designation1, Designation2, Designation3) AS
> SELECT TOP 100 PERCENT CustName, CustType, CustTable, CustActive, CASE WHEN
> CustType = 'A' THEN CAST(1 AS BIT) AS IsLvl1, dbo.ConcatCustName(CustName,
> CustType, CustActive) AS OtherCustInfo FROM dbo.CustView1 ORDER BY
> Designation1, Designation2, Designation3
>
> In the actual 2nd view I also use LTRIM, RTRIM, NULLIF, ISNULL and COALESCE
> if that makes any difference.
>
> Why are Designation1, Designation2, Designation3 required in the column list
> of the 2nd view?
>
> I'm not finding the answer in BOL. -->
>
> column
>
> Is the name to be used for a column in a view. Naming a column in CREATE
> VIEW is necessary only when a column is derived from an arithmetic
> expression, a function, or a constant, when two or more columns may
> otherwise have the same name (usually because of a join), or when a column
> in a view is given a name different from that of the column from which
> derived. Column names can also be assigned in the SELECT statement.
>
> If column is not specified, the view columns acquire the same names as the
> columns in the SELECT statement.
>
>
>
> Thanks,
>
> Keith
>
>
>

14 Jul 2006 1:59 PM

Alejandro Mesa

Sorry for the bad example used.

Show quote

> Example:
>
> use northwind
> go
>
> create view dbo.v1
> as
> select orderid, customerid, getdate() as orderdate
> from dbo.orders
> go
>
> create view dbo.v2
> as
> select top 100 percent orderid, customerid
> from dbo.v1
> order by orderdate
> go
>
> set showplan_text on
> go
>
> select *
> from dbo.v2
> go
>
> set showplan_text on
> go
>
> drop view dbo.v2, dbo.v1
> go

AMB

Show quote

"Alejandro Mesa" wrote:

> Keith G Hicks,
>
> 1 - What is the meaning of using "order by" clause inside a view, if SQL
> Server will not guaranteed that the result of selecting from the view will be
> ordered if you do not use the "order by" clause in the "select" statement
> referencing the view.
>
> Example:
>
> use northwind
> go
>
> create view dbo.v1
> as
> select orderid, customerid, getdate() as orderdate
> from dbo.orders
> go
>
> create view dbo.v2
> as
> select top 100 percent orderid, customerid
> from dbo.v1
> order by orderdate
> go
>
> set showplan_text on
> go
>
> select *
> from dbo.v2
> go
>
> set showplan_text on
> go
>
> drop view dbo.v2, dbo.v1
> go
>
> Result:
>
>   |--Sort(ORDER BY:([Expr1003] ASC))
>        |--Compute Scalar(DEFINE:([Expr1003]=getdate()))
>             |--Index Scan(OBJECT:([northwind].[dbo].[Orders].[CustomerID]))
>
> Notice that the result will be ordered by "CustomerID" and not by [orderdate].
>
>
> 2 - Are you using "select distinct" in the definition of the second view?
>
> Is that is the case, then take a look to the "create view" statement in BOL.
>
> Example reproducing the error:
>
> use northwind
> go
>
> create view dbo.v1
> as
> select 1 as c1, 0 as c2
> union all
> select 2, 1
> go
>
> create view dbo.v2
> as
> select distinct top 100 percent c1
> from dbo.v1
> order by c2
> go
>
> select *
> from dbo.v2
> go
>
> drop view dbo.v2, dbo.v1
> go
>
> Result:
>
> Server: Msg 145, Level 15, State 1, Procedure v2, Line 4
> ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
>
>
> AMB
>
> "Keith G Hicks" wrote:
>
> > I recently created a view (based on another view) that has an ORDER BY in it
> > that is requiring that the ORDER BY columns be listed in the column list
> > after the view name. This is the first time I've encountered this and am
> > wondering why they are required to be in the list even though they do not
> > appear in the SELECT part of the view. I am not permitted to post the code
> > here, but here's a similar example:
> >
> > View 1:
> >
> > CREATE VIEW dbo.CustView1 (CustName, CustType, CustTable, CustActive,
> > Designation1, Designation2, Designation3 ) AS
> > SELECT CustName, CustType, 'A' AS CustTable, CAST(0 AS BIT) AS CustActive,
> > CustID AS Designation1, 0 AS Designation2, 0 AS Designation3 FROM Custs1
> > WHERE CustType <> 'X'
> > UNION ALL
> > SELECT CustName, CustType, 'B' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
> > AS Designation1, CustID AS Designation2, 0 AS Designation3 FROM Custs2
> > WHERE CustType <> 'X'
> > UNION ALL
> > SELECT CustName, CustType, 'C' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
> > AS Designation1, 0 AS Designation2, CustID AS Designation3 FROM Custs3
> > WHERE CustType <> 'X'
> >
> > View 2:
> >
> > CREATE VIEW dbo.CustView2 (CustName, CustType, CustTable, CustActive,
> > IsLvl1, OtherCustInfo, Designation1, Designation2, Designation3) AS
> > SELECT TOP 100 PERCENT CustName, CustType, CustTable, CustActive, CASE WHEN
> > CustType = 'A' THEN CAST(1 AS BIT) AS IsLvl1, dbo.ConcatCustName(CustName,
> > CustType, CustActive) AS OtherCustInfo FROM dbo.CustView1 ORDER BY
> > Designation1, Designation2, Designation3
> >
> > In the actual 2nd view I also use LTRIM, RTRIM, NULLIF, ISNULL and COALESCE
> > if that makes any difference.
> >
> > Why are Designation1, Designation2, Designation3 required in the column list
> > of the 2nd view?
> >
> > I'm not finding the answer in BOL. -->
> >
> > column
> >
> > Is the name to be used for a column in a view. Naming a column in CREATE
> > VIEW is necessary only when a column is derived from an arithmetic
> > expression, a function, or a constant, when two or more columns may
> > otherwise have the same name (usually because of a join), or when a column
> > in a view is given a name different from that of the column from which
> > derived. Column names can also be assigned in the SELECT statement.
> >
> > If column is not specified, the view columns acquire the same names as the
> > columns in the SELECT statement.
> >
> >
> >
> > Thanks,
> >
> > Keith
> >
> >
> >

14 Jul 2006 5:21 PM

Alejandro Mesa

May be we can use this example.

use northwind
go

select cast(orderid as int) as orderid, orderdate, customerid
into dbo.t1
from dbo.orders
go

create unique clustered index t1_orderid_u_c_ix
on dbo.t1(orderid desc)
go

create view dbo.v1
as
select orderid, orderdate
from dbo.t1
go

create view dbo.v2
as
select top 100 percent
    oh.orderid,
    orderdate,
    od.quantity,
    od.unitprice,
    od.discount
from
    dbo.v1 as oh
    inner join
    dbo.[order details] as od
    on oh.orderid = od.orderid
order by
    oh.orderdate asc
go

select
    orderid,
    orderdate,
    sum(cast((quantity * unitprice) * (1.00 - discount) as money)) as ext_price
from
    dbo.v2
group by
    orderid, orderdate
go

drop view dbo.v2, dbo.v1
go

drop table dbo.t1
go

AMB

Show quote

"Alejandro Mesa" wrote:

> Sorry for the bad example used.
>
> > Example:
> >
> > use northwind
> > go
> >
> > create view dbo.v1
> > as
> > select orderid, customerid, getdate() as orderdate
> > from dbo.orders
> > go
> >
> > create view dbo.v2
> > as
> > select top 100 percent orderid, customerid
> > from dbo.v1
> > order by orderdate
> > go
> >
> > set showplan_text on
> > go
> >
> > select *
> > from dbo.v2
> > go
> >
> > set showplan_text on
> > go
> >
> > drop view dbo.v2, dbo.v1
> > go
>
>
> AMB
>
> "Alejandro Mesa" wrote:
>
> > Keith G Hicks,
> >
> > 1 - What is the meaning of using "order by" clause inside a view, if SQL
> > Server will not guaranteed that the result of selecting from the view will be
> > ordered if you do not use the "order by" clause in the "select" statement
> > referencing the view.
> >
> > Example:
> >
> > use northwind
> > go
> >
> > create view dbo.v1
> > as
> > select orderid, customerid, getdate() as orderdate
> > from dbo.orders
> > go
> >
> > create view dbo.v2
> > as
> > select top 100 percent orderid, customerid
> > from dbo.v1
> > order by orderdate
> > go
> >
> > set showplan_text on
> > go
> >
> > select *
> > from dbo.v2
> > go
> >
> > set showplan_text on
> > go
> >
> > drop view dbo.v2, dbo.v1
> > go
> >
> > Result:
> >
> >   |--Sort(ORDER BY:([Expr1003] ASC))
> >        |--Compute Scalar(DEFINE:([Expr1003]=getdate()))
> >             |--Index Scan(OBJECT:([northwind].[dbo].[Orders].[CustomerID]))
> >
> > Notice that the result will be ordered by "CustomerID" and not by [orderdate].
> >
> >
> > 2 - Are you using "select distinct" in the definition of the second view?
> >
> > Is that is the case, then take a look to the "create view" statement in BOL.
> >
> > Example reproducing the error:
> >
> > use northwind
> > go
> >
> > create view dbo.v1
> > as
> > select 1 as c1, 0 as c2
> > union all
> > select 2, 1
> > go
> >
> > create view dbo.v2
> > as
> > select distinct top 100 percent c1
> > from dbo.v1
> > order by c2
> > go
> >
> > select *
> > from dbo.v2
> > go
> >
> > drop view dbo.v2, dbo.v1
> > go
> >
> > Result:
> >
> > Server: Msg 145, Level 15, State 1, Procedure v2, Line 4
> > ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
> >
> >
> > AMB
> >
> > "Keith G Hicks" wrote:
> >
> > > I recently created a view (based on another view) that has an ORDER BY in it
> > > that is requiring that the ORDER BY columns be listed in the column list
> > > after the view name. This is the first time I've encountered this and am
> > > wondering why they are required to be in the list even though they do not
> > > appear in the SELECT part of the view. I am not permitted to post the code
> > > here, but here's a similar example:
> > >
> > > View 1:
> > >
> > > CREATE VIEW dbo.CustView1 (CustName, CustType, CustTable, CustActive,
> > > Designation1, Designation2, Designation3 ) AS
> > > SELECT CustName, CustType, 'A' AS CustTable, CAST(0 AS BIT) AS CustActive,
> > > CustID AS Designation1, 0 AS Designation2, 0 AS Designation3 FROM Custs1
> > > WHERE CustType <> 'X'
> > > UNION ALL
> > > SELECT CustName, CustType, 'B' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
> > > AS Designation1, CustID AS Designation2, 0 AS Designation3 FROM Custs2
> > > WHERE CustType <> 'X'
> > > UNION ALL
> > > SELECT CustName, CustType, 'C' AS CustTable, CAST(0 AS BIT) AS CustActive, 0
> > > AS Designation1, 0 AS Designation2, CustID AS Designation3 FROM Custs3
> > > WHERE CustType <> 'X'
> > >
> > > View 2:
> > >
> > > CREATE VIEW dbo.CustView2 (CustName, CustType, CustTable, CustActive,
> > > IsLvl1, OtherCustInfo, Designation1, Designation2, Designation3) AS
> > > SELECT TOP 100 PERCENT CustName, CustType, CustTable, CustActive, CASE WHEN
> > > CustType = 'A' THEN CAST(1 AS BIT) AS IsLvl1, dbo.ConcatCustName(CustName,
> > > CustType, CustActive) AS OtherCustInfo FROM dbo.CustView1 ORDER BY
> > > Designation1, Designation2, Designation3
> > >
> > > In the actual 2nd view I also use LTRIM, RTRIM, NULLIF, ISNULL and COALESCE
> > > if that makes any difference.
> > >
> > > Why are Designation1, Designation2, Designation3 required in the column list
> > > of the 2nd view?
> > >
> > > I'm not finding the answer in BOL. -->
> > >
> > > column
> > >
> > > Is the name to be used for a column in a view. Naming a column in CREATE
> > > VIEW is necessary only when a column is derived from an arithmetic
> > > expression, a function, or a constant, when two or more columns may
> > > otherwise have the same name (usually because of a join), or when a column
> > > in a view is given a name different from that of the column from which
> > > derived. Column names can also be assigned in the SELECT statement.
> > >
> > > If column is not specified, the view columns acquire the same names as the
> > > columns in the SELECT statement.
> > >
> > >
> > >
> > > Thanks,
> > >
> > > Keith
> > >
> > >
> > >

14 Jul 2006 1:36 PM

David Portas

Keith G Hicks wrote:

> I recently created a view (based on another view) that has an ORDER BY in it
> that is requiring that the ORDER BY columns be listed in the column list
> after the view name. This is the first time I've encountered this and am
> wondering why they are required to be in the list even though they do not
> appear in the SELECT part of the view. I am not permitted to post the code
> here, but here's a similar example:
>

This looks like one manifestation of a known bug with ORDER BY in SQL
Server 2000. Fortunately you don't need ORDER BY in that view - in fact
it's a mistake to put it in there. The ordering of any query result is
always undefined unless you specify ORDER BY in the QUERY itself. That
applies equally whether the source for your query is a table or a view.
Views are unordered sets in exactly the same way that tables are.

Specifying TOP 100 PERCENT does nothing useful in the view so you
should remove both it and ORDER BY.

17 Jul 2006 3:28 PM

Keith Kratochvil

I don't understand why people feel they need to order views. Why not just
specify the ORDER BY when you retrieve the data?

I just ran this within my SQL Server 2005 SP1 server without any issues

use adventureworks
go

create view foo
as
select top 100 percent * From HumanResources.Department order by GroupName
go

/*well, there was one issue....when I retrieved the data from the view it
was not ordered. I had to specify the ORDER BY clause after this statement
in order to have the data ordered as desired*/
select * from foo

go
drop view foo

--
Keith Kratochvil

Show quote

"Keith G Hicks" <k**@comcast.net> wrote in message
news:ux2XuK0pGHA.4188@TK2MSFTNGP04.phx.gbl...
>I recently created a view (based on another view) that has an ORDER BY in
>it
> that is requiring that the ORDER BY columns be listed in the column list
> after the view name. This is the first time I've encountered this and am
> wondering why they are required to be in the list even though they do not
> appear in the SELECT part of the view. I am not permitted to post the code
> here, but here's a similar example:
>
> View 1:
>
> CREATE VIEW dbo.CustView1 (CustName, CustType, CustTable, CustActive,
> Designation1, Designation2, Designation3 ) AS
> SELECT CustName, CustType, 'A' AS CustTable, CAST(0 AS BIT) AS CustActive,
> CustID AS Designation1, 0 AS Designation2, 0 AS Designation3 FROM Custs1
> WHERE CustType <> 'X'
> UNION ALL
> SELECT CustName, CustType, 'B' AS CustTable, CAST(0 AS BIT) AS CustActive,
> 0
> AS Designation1, CustID AS Designation2, 0 AS Designation3 FROM Custs2
> WHERE CustType <> 'X'
> UNION ALL
> SELECT CustName, CustType, 'C' AS CustTable, CAST(0 AS BIT) AS CustActive,
> 0
> AS Designation1, 0 AS Designation2, CustID AS Designation3 FROM Custs3
> WHERE CustType <> 'X'
>
> View 2:
>
> CREATE VIEW dbo.CustView2 (CustName, CustType, CustTable, CustActive,
> IsLvl1, OtherCustInfo, Designation1, Designation2, Designation3) AS
> SELECT TOP 100 PERCENT CustName, CustType, CustTable, CustActive, CASE
> WHEN
> CustType = 'A' THEN CAST(1 AS BIT) AS IsLvl1, dbo.ConcatCustName(CustName,
> CustType, CustActive) AS OtherCustInfo FROM dbo.CustView1 ORDER BY
> Designation1, Designation2, Designation3
>
> In the actual 2nd view I also use LTRIM, RTRIM, NULLIF, ISNULL and
> COALESCE
> if that makes any difference.
>
> Why are Designation1, Designation2, Designation3 required in the column
> list
> of the 2nd view?
>
> I'm not finding the answer in BOL. -->
>
> column
>
> Is the name to be used for a column in a view. Naming a column in CREATE
> VIEW is necessary only when a column is derived from an arithmetic
> expression, a function, or a constant, when two or more columns may
> otherwise have the same name (usually because of a join), or when a column
> in a view is given a name different from that of the column from which
> derived. Column names can also be assigned in the SELECT statement.
>
> If column is not specified, the view columns acquire the same names as the
> columns in the SELECT statement.
>
>
>
> Thanks,
>
> Keith
>
>

17 Jul 2006 3:50 PM

Also check out the execution plan.

ML

---
http://milambda.blogspot.com/

17 Jul 2006 4:05 PM

Paul Ibison

Keith - I agree that the only way to guarantee any order is to include an
ORDER BY in the outer-most query, however I don't follow what your example
is trying to show :). What I would expect is:

use adventureworks
go

create view foo
as
select * From HumanResources.Department
go

select * from foo
order by GroupName

(BTW IMO it's not good practice to use select * in a view. If new columns
are added to the object HumanResources.Department after the foo view has
been created, they won't get picked up in the foo view because syscolumns
contains the real view definition, and this can be very confusing for
users/GUIs).

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

17 Jul 2006 4:15 PM

Paul Ibison

Apologies Keith - my mailreader (Outlook Express) is playing up and it
seemed yours is the first post in this thread - didn't realise it was a
reply.
Cheers,
Paul Ibison

17 Jul 2006 4:07 PM

Steve Kass

"Keith Kratochvil" <sqlguy.bac***@comcast.net> wrote in message
news:On5cYXbqGHA.3484@TK2MSFTNGP04.phx.gbl...
>I don't understand why people feel they need to order views. Why not just
>specify the ORDER BY when you retrieve the data?

Blame the SQL Server design tools, which allow people to add an order by
clause to a view (and then add the TOP 100 PERCENT .. ORDER BY). Or
blame Microsoft for choosing confusing syntax for their proprietary TOP
extension. Instead of using a final ORDER BY (which already had a meaning
in
standard SQL) as part of the TOP specification, they should have done
something more like the analytical function syntax:

SELECT TOP (20 ROWS) OVER (ORDER BY a)
a,b,c
FROM T

Then a final ORDER BY after the table would only have one meaning,
and the tools could enforce "no ordered views"

Steve Kass
Drew University

Show quote

>
> I just ran this within my SQL Server 2005 SP1 server without any issues
>
> use adventureworks
> go
>
> create view foo
> as
> select top 100 percent * From HumanResources.Department order by GroupName
> go
>
> /*well, there was one issue....when I retrieved the data from the view it
> was not ordered. I had to specify the ORDER BY clause after this
> statement in order to have the data ordered as desired*/
> select * from foo
>
>
>
> go
> drop view foo
>
>
>
> --
> Keith Kratochvil
>
>
> "Keith G Hicks" <k**@comcast.net> wrote in message
> news:ux2XuK0pGHA.4188@TK2MSFTNGP04.phx.gbl...
>>I recently created a view (based on another view) that has an ORDER BY in
>>it
>> that is requiring that the ORDER BY columns be listed in the column list
>> after the view name. This is the first time I've encountered this and am
>> wondering why they are required to be in the list even though they do not
>> appear in the SELECT part of the view. I am not permitted to post the
>> code
>> here, but here's a similar example:
>>
>> View 1:
>>
>> CREATE VIEW dbo.CustView1 (CustName, CustType, CustTable, CustActive,
>> Designation1, Designation2, Designation3 ) AS
>> SELECT CustName, CustType, 'A' AS CustTable, CAST(0 AS BIT) AS
>> CustActive,
>> CustID AS Designation1, 0 AS Designation2, 0 AS Designation3 FROM Custs1
>> WHERE CustType <> 'X'
>> UNION ALL
>> SELECT CustName, CustType, 'B' AS CustTable, CAST(0 AS BIT) AS
>> CustActive, 0
>> AS Designation1, CustID AS Designation2, 0 AS Designation3 FROM Custs2
>> WHERE CustType <> 'X'
>> UNION ALL
>> SELECT CustName, CustType, 'C' AS CustTable, CAST(0 AS BIT) AS
>> CustActive, 0
>> AS Designation1, 0 AS Designation2, CustID AS Designation3 FROM Custs3
>> WHERE CustType <> 'X'
>>
>> View 2:
>>
>> CREATE VIEW dbo.CustView2 (CustName, CustType, CustTable, CustActive,
>> IsLvl1, OtherCustInfo, Designation1, Designation2, Designation3) AS
>> SELECT TOP 100 PERCENT CustName, CustType, CustTable, CustActive, CASE
>> WHEN
>> CustType = 'A' THEN CAST(1 AS BIT) AS IsLvl1,
>> dbo.ConcatCustName(CustName,
>> CustType, CustActive) AS OtherCustInfo FROM dbo.CustView1 ORDER BY
>> Designation1, Designation2, Designation3
>>
>> In the actual 2nd view I also use LTRIM, RTRIM, NULLIF, ISNULL and
>> COALESCE
>> if that makes any difference.
>>
>> Why are Designation1, Designation2, Designation3 required in the column
>> list
>> of the 2nd view?
>>
>> I'm not finding the answer in BOL. -->
>>
>> column
>>
>> Is the name to be used for a column in a view. Naming a column in CREATE
>> VIEW is necessary only when a column is derived from an arithmetic
>> expression, a function, or a constant, when two or more columns may
>> otherwise have the same name (usually because of a join), or when a
>> column
>> in a view is given a name different from that of the column from which
>> derived. Column names can also be assigned in the SELECT statement.
>>
>> If column is not specified, the view columns acquire the same names as
>> the
>> columns in the SELECT statement.
>>
>>
>>
>> Thanks,
>>
>> Keith
>>
>>
>
>