Technical Article

Sorted views in 2005 and TOP clause

,

Since the trick with TOP 100 PERCENT, which in most cases worked on Sql Server 2000 when You wanted to have sorted views or inline functions, does not work anymore on Sql Server 2005 due to "smarter" query optimizer, You have to look for another solution.

Here is one of them:

instead on TOP 100 PERCENT clause You can use TOP 9223372036854775807, where the big number is maximum value for bigint data type. If You use greater number then You will receive an error message 'Arithmetic overflow error converting expression to data type bigint' but in all likelihood You do not have such number of records in the table.

Another dirty trick is to use TOP 99.999999999999999999999999999999999999 PERCENT (38 nines - maximum allowed precision for decimal data type) clause which also worked good enough for my needs.

-- this does not work (at least not on my SQL Server 2005 with SP2)

create view not_working_order_by
as
select top 100 percent
*
from 
sys.tables
order by 
name
go

select * from not_working_order_by
go

drop view not_working_order_by
go

-- but this works (of course on my SS2005 with SP2 :);

create view hopefully_working_order_by
as
select top 9223372036854775807 
*
from 
sys.tables
order by 
name

go

select * from hopefully_working_order_by
go

drop view hopefully_working_order_by
go

--- this one should work as well

create view hopefully_working_order_by_sec
as
select top 99.999999999999999999999999999999999999 percent 
*
from 
sys.tables
order by 
name

go

select * from hopefully_working_order_by_sec
go

drop view hopefully_working_order_by_sec
go

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.83 (6)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.83 (6)

You rated this post out of 5. Change rating

Related content