TheSQLGuru (5/5/2010)
Well, the BEST answer is for MS to fix the damn optimizer to work properly with partitions in ALL ASPECTS!! 🙂
Oh I agree - but to be fair to the optimiser, the main problem in 2005 was the design choice made to implement the partition selection using - yes - a correlated loop join. The optimiser has no choice, sadly 🙁
SQL Server 2008 eliminates the problem using a far better design (partition as hidden leading column of every index), and as far as I know, most people* are happy with the very performant partitioning in 2008 and later.
One of my favourite things about the 2008 implementation is that it is now possible to perform an 'index seek' on a heap!
But they are too busy building stuff that no one (or maybe 0.03% of installed base) will EVER use . . . sigh . . . :angry:
Tell me about it. I haven't even touched every new 'feature' of 2008 yet and R2 is out!
edit: *except Tim 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi