SQL: Be aware of views with “Select Top 100 Percent”

In SQL Server 2003 and 2005, Microsoft made a rather subtle change to the query engine, which was to remove the “TOP” phrase from the select clause, if the top value is “100 PERCENT”. This makes perfect sense, as using “select top 100 percent …” is no different from simply using “select …” – that is the default behavior without a TOP phrase. So, what’s the problem?

Well, the problem relates to views in a SQL Server database. First, a little background. In SQL Server, as in most RDBMS’, views are a mechanism for defining a horizontal and vertical “slice” of a table (or virtual table) – a subset of the columns and rows of data. Views, in theory, are not sorted – the same way that tables are not sorted. If you want a collection of rows from a view, you add an ORDER BY clause to the select statement when you query rows from the view.  SQL Server even enforces this, by preventing creation of a view using a sql query which includes an ORDER BY  clause.

But there are scenarios  (some legitimate, some not so much) in which you may really want to force a sort on the rows of your view. And in SQL Server 2000 and earlier, you could do this via a bit of a hack – by defining your view query using “select TOP 100 PERCENT”.

An aside: Why would that work? Well, consider the case in which your view should only include the first 25% percent of the rows in a table – well, the “top 25%” only makes sense in the context of a result set which is sorted, right? (What does the “top 25%” from my employee table mean? The top by alphabetical name? By age? By hat size?) So, SQL Server allows this as a special case – you can use an ORDER BY clause in the definition of a view IF you also include a “TOP” phrase in th select clause. So, if you really wanted to force a sort on a view, but you wanted to include all of the records returned by your query, you could “trick” SQL Server into allowing this by adding the “TOP 100 PERCENT.”

So, back to the problem – as of SQL Server 2003, the SQL engine is now (in something of a pre-compile step), removing the TOP 100 PERCENT from any sql queries. And any views which existed in a SQL 2000 or earlier database which was upgraded, which made use of this little trick, would internally have the TOP phrase remove, and then also have the ORDER BY removed. The end result – the same view, but no longer sorted. And if your applications were assuming queries against that view would always be sorted, you now have a broken application.

The “correct” way to resolve this depends on the situation. If possible, the better solution is to add any sorting in the SQL statement which queries the view. But if you do absolutely need the sorting in the view definition, then you can simply change that “SELECT TOP 100 PERCENT” to “SELECT TOP 99.999 PERCENT” or “SELECT TOP 2147483647″ (the max integer size)  - just bear in mind, you are trading one hack for another.

Advertisement