TOP 100 PERCENT and ORDER BY obsolete from SQL Server 2005

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