SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Sorted views in 2005 and TOP clause

By Rafal Skotak,

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.

Total article views: 1257 | Views in the last 30 days: 7
Related Articles


Alan Burstein discusses a better performning alternative to PERCENT_RANK that works on SQL Server ve...


SQL Server – "Denali" – PERCENT_RANK() Analytic Function

PERCENT_RANK() returns the position of a row within the result set. In contrast to RANK() function, ...


how to you make this 44.8730084 into this: 44.8%

just need these numbers in percents


Format values as Percent

Sorting issue on columns having values in percent format