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