In SQL 2000 it was an attempt to return all
records of the view in the correct order. In SQL 2005 and up you can not
simulate ORDER BY using this trick anymore, so SELECT TOP (100) PERCENT has no
meaning.
Lets stimulate the scenerio. We create a table testTop100
for our explanation with field id
and name
create table testTop100
(id int identity(1,1),
name varchar(10))
Command(s) completed successfully.
Lets insert few records to testTop100
insert into testTop100 values('Abhaya')
insert into testTop100 values('Nitya')
insert into testTop100 values('Ananya')
insert into testTop100 values('Roma')
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Now we will be creating view vwTestTop100
with top 100 Percent and Order by clause
create view vwTestTop100
as
select top 100 percent * from testTop100 order by id desc
go
Command(s) completed successfully.
Lets see the result with simple select query
select * from testTop100
id name
----------- ----------
1 Abhaya
2 Nitya
3 Ananya
4 Roma
(4 row(s) affected)
Now lets see the result of View which is using Top 100 Percent and order by
on id desc
select * from vwTestTop100
id name
----------- ----------
1 Abhaya
2 Nitya
3 Ananya
4 Roma
(4 row(s) affected)
As we can see SQL Server neither throws error nor it acknowledge the presence
of order by clause, it simply ignore the order by clause in View defination.
Now lets try the following
select * from vwTestTop100 order by id desc
id name
----------- ----------
4 Roma
3 Ananya
2 Nitya
1 Abhaya
(4 row(s) affected)
Now from the output we can see, desired result found
Conclusion : It is pointless to add ORDER BY clause
to the view definition and expect records to come in that ORDER. If you need
records ordered, don't put ORDER BY in the view, but rather
select *
from myView ORDER BY OrderFields.
This way the
correct order will be guaranteed.
Abide by code semantic, u miss all coding fun..Walk off beyond
Post Reference: Vikram Aristocratic Elfin Share
