Much of the work I've been doing lately is DBA type of work rather than development work. Even though I'm a SQL Server MVP, I'm still a newbie when it comes to managing databases where there are lots of developers and users. Because of this I've become a huge fan of fellow MVP Kimberly Tripp. I've watched her TechEd session on partitioning at least 10 ten times and I have used her techniques with great success. One of the best examples was a table which I recreated using horizontal partitioning.
The table had ~280 million rows and used up about 96 GB of space. The data stored in the table was historical data and a simple queries to get the number of rows for a certain date would take approx. 30 minutes. I redesigned the table (optimizing many of the column sizes) and used one table per year and a view to join them all together. The new tables together took up about 25 GB of space. Additionally, since I'm using multiple tables I can also only backup the most recent year which saves even more space since I'm using multiple files. Here is the real kicker though:
The simple query that took over 30 minutes now takes about 15 seconds.
Another example was using vertical partitioning. In this case there was a single table that was populated using data from multiple sources. There was a DTS job that populated the table which ran daily and took approx. 5-6 hours to run. Using vertical partitioning (meaning indexed views) I was able to create one table per data source and then join them with a schemabound view. Addionally I added many more datasources. The result:
The new DTS job completes in just over an hour.
The was due to the fact that I didn't have to pull down the data and do a lot of updates. Instead I could just truncate all the tables, pull in the data, and create my indexed view.
I highly recommend reading Kimberly's blog if you're not already and I would also suggest watching her TechEd sessions (if you have the DVD, if not you can buy it). She also has produced a webcast on indexes which you can watch for free and is also very good. There is lots of good info at her website as well.