Yes, I agree that partitioning is very useful and should be strongly considered by anyone that is working with large data sets. I am using partitions and still find breaking large operation into smaller pieces to be very useful. In my situation, I have SQL statements that operate on huge portions of the partitions and cause a lot of table locks within the partitions themselves. These locks cause too much contention with the production system and are not feasible for the business.

When I use this technique on a partitioned table, I always order the records primarily by the partition key. This further reduces lock contention and allows SQL Server to perform well by leveraging the clustered index.

Even in the case when partitions are being used to perform operations "offline", breaking large SQL into smaller pieces is useful. For example, for some SQL operations, I switch select partitions into an "offline" table which eliminates any lock contention by any operations against those partitions from the production system. I also drop all unnecessary indexes in the "offline" table so that the operation will run much faster. Even for these "offline" partitions, I have found that breaking large SQL operations into smaller pieces is helpful. So, instead of inserting 10,000,000 rows in one shot, I use this technique to insert 20 sets of 500,000 rows. This causes less system resources to be used at a time and allows any other processes running on the same database server to run better. An additional benefit is that the operation can be stopped and started mid-stream which is helpful if it is an operation that takes hours rather than minutes to run.