Well, it’s been a GREAT week here in Switzerland while working with my partner Trivadis. Today, we wrapped up a two-day course on Designing for Performance (in Geneva) while on Monday/Tuesday we did a two-day course on Indexing for Performance in Zurich. The food, the wine, the cheese, the butter, yum! Oh… and the questions/comments/technical focus, etc. has all been great. :) I’m flying home today (Sat) so wish me luck on having internet access at 36,000 feet again (probably not…I’m flying United instead of Lufthansa – and it’s only Lufthansa that has FlyNet). Wow – can you imagine where we’re going to be in only a couple more years? Internet access everywhere! (hmmm.. how do we escape? well, that’s another blog entry for another day :)

Anyway, one of the great things about teaching is that I get to meet all sorts of people and work through all sorts of interesting problems… And – this blog entry is based on a discussion I had with [a very blogless ;-]Meinrad Weiss – a Trivadis employee/consultant AND a fellow RD. (I was bullied into blogging by CV so now I do my part and do the same to others)

I can’t remember how it started but somehow a discussion started on Top 100 PERCENT being used in views. I mentioned that while this was a good trick in SQL Server 2000, it has been REMOVED from SQL Server 2005 (meaning that TOP 100 PERCENT does NOT order the data within a view). Theoretically, I agree with this decision – data within a view should NOT be ordered. A view should SOLELY define a tabular set. It is up to the query which is accessing the view to define the presentation of the view. Using TOP within a view should be limited to ONLY when it is used to further define the data set (i.e. TOP 10 PERCENT… ORDER BY TotalSales DESC makes perfect sense).

Now, having said that – it was a cool trick – but with Pros/Cons. The obvious Pro is simplicity in access. While adding the ORDER BY to the query against the view really isn’t all that difficult, it does make it a bit easier for quick/simple query access. BUT – there’s a HUGE con here too. If the view starts getting used for other purposes (like in joins to other tables), then the being ordered before the joins, etc. can cause you an additional step that is NOT necessary. As a result, performance was compromised.

Long story short, I generally recommended against it but it was still cool. So – then Meinrad started playing and came up with – what about 99.9 on a table that has < 100 rows OR 99.99% on a table that has < 1000 rows, etc. And – yes – that DOES work, because SQL Server rounds to 100%. So, you are back to getting 100% of your data, ORDERED within a view. But – you need to set your percentage to an appropriate percentage based on rows – but what if you don’t know the row count? How about TOP n where n = the max value for a bigint (9,223,372,036,854,775,807)?? That should always work…and it does.

OK – so what’s the point? Yes, we DO have a workaround for the removal of TOP 100 PERCENT in SQL Server 2005 – but be CAREFUL – you are potentially shooting yourself in the foot. If this view is NEVER used for anything but SELECT * FROM View, you’re OK. If you start adding joins, etc. then you might get into trouble. In the showplan below – the data returned is EXACTLY the same.

5 Responses to SQL 2000 v. 2005 – Using Top 100 Percent in a View

I actually used to use this to -enhance- performance in some cases, by adding it to derived tables to force intermediate materialization in cases when the optimizer flattened things out a bit too much. I’m a bit disappointed to see it removed, for that reason…

Kim, You have excellent timing. I just came up against this issue last week and having not heard anything about this not working in 2005 it took a while to track down. I agree with you (and the SQL team) entirely about the fact that Views shouldn’t be ordered, I hadn’t given it much thought previously but it does makes sense. However we are still running 2000 databases everywhere (clients aren’t real keen to upgrade for no apparent reason) so when we hit a client that does have a 2005 server we just attach our 2000 databases and run them in 2000 (8.0) compatibility mode. My issue is that the old Order By behaviour in Views doesn’t work, the 8.0 compatibility mode doesn’t seem to be completely 8.0 compatible. Is this something you have come across yourself? Is there a setting somewhere else that can be altered to make Order By work in a view? I realise I could do the Top 99.99 Percent trick, but I’m asking more because I’m curious than anything else, we plan to remove the few Ordered views we have in our database as soon as possible. It just seems to me that 8.0 compatible should mean exactly that.

hi Kim
thanks for the article.btw i’ve quick question unlike what you suggested in the article am using "row_number()" function to get the order i want. is it good ? bad ? any comments. see my sample view below.Thanks