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

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.



SELECT C.ContactName, Sub.*
FROM OrderSubTotalsViewOrdered2 AS Sub
   
JOIN Orders AS O ON O.orderid = Sub.Orderid
   JOIN Customers AS c ON c.customerid = o.customerid
WHERE C.City = ‘Madrid’
ORDER BY SubTotal DESC
go


SELECT C.ContactName, Sub.*
FROM OrderSubTotalsViewNOTOrdered AS Sub
   
JOIN Orders AS O ON O.orderid = Sub.Orderid
   JOIN Customers AS c ON c.customerid = o.customerid
WHERE C.City = ‘Madrid’
ORDER BY SubTotal DESC
go


BUT – the first plan of execution queries against the ORDERED set and the second against the un-ordered. Check out the showplans below:



This is a VERY COMPELLING reason to BE CAREFUL ordering data within a view. While the trick does work, please use it sparingly.


If you want to play with the views created above, you’ll need a copy of the Northwind Database and you’ll need this script: Top 100 Percent in SQL Server 2005.sql (4.46 KB).


Have fun!
kt


PS – I’m adding my blog to Technorati so I need to post their link to start generating my profile… here we go: Technorati Profile

5 thoughts on “SQL 2000 v. 2005 – Using Top 100 Percent in a View

  1. 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…

  2. 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.

  3. Sorry about that. We "upgraded" my blog just after that post and it appears to have broken a few links. I’ll certainly look around for more but thanks for this post to let me know!

    Thanks!
    kt

  4. 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

    CREATE VIEW Transactions
    AS
    SELECT
    [RowNumber_id] = ROW_NUMBER() OVER (ORDER BY tr.transactid DESC) ,
    [Is A Consultation] = CASE tr.[type] WHEN ‘I’ THEN ‘Yes’ ELSE ‘No’ END,
    [Bulk Billed] = CASE tr.[bulk] WHEN 1 THEN ‘Yes’ ELSE ‘No’ END,
    [Created Date & Time] = tr.[date],
    [Last Modified By] = tr.[modifier],
    tr.[transactid],
    tr.[fk_master_masterid] AS [MasterId]
    FROM [dbo].[transactions] AS tr
    WHERE tr.[status] < 2

Leave a Reply to Kimberly L. Tripp Cancel reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.