Great article. I just wanted to say that I have had some very large performance gains to query times when employing horizontal partitioning on biggish fact tables in a datawarehouse environment using SQL2000. When I first looked into this, a number of seasoned SQL professionals at my workplace had never heard of the 'partitioned view' functionality in SQL2000, allowing the 'union all' view over the tables to essentially mimic full table functionality, including provisions for insert/update/deletes without specifiying which underlying table we need to work on. My workmates didn't believe this was achievable until I put an example together, showing differences in query plans. Once I did this, they were mighty impressed (with the functionality, not just me!).
As mentioned in the article, other benefits available including shortening the time window required for backup and re-indexing. We also got these benefits thrown in for free, even though we were only targetting query performance.
So I encourage everyone to eagerly await the rest of the articles, as I will be.
Matt.
Great article Andy, thanks for taking the time to go over this topic (very timely for me ).
I'm looking forward to more depth and details soon!
-Ron
This is a very good article.
I just have one question or clarification. When you say
“In SQL 2005 we have true partitions, where we can define a table as residing on a partition and all the sub tables are managed by SQL, there is no view.”
Isn’t this only in SQL 2005 Enterprise Edition?
this article is good
i want some help to improve the performance of DTS package that holding Store procedure and before connection to flate file drpping the index and after that recreating the index.
In which are i have to work out for improving performance
