Michael Valentine Jones (6/21/2012)
In conjunction with this, they could have the web application query the table using the snapshot isolation level (or set the database to read committed snapshot) so that the web app will still be able to see the data that was committed before the update transaction started.That would eliminate even momentary blocking, and be much simple to implement than a partitioned table.
I haven't read the rest of the posts on this thread yet but I agree with the above. Partitioning isn't as easy as some would have you believe. For example, if you have any unique indexes on the table, the partitioning key will automatically be added which makes them {drum roll please} non-unique based on the original unique column(s). It also makes FKs to the table damned near impossible for the same reason unless the only UNIQUE index is also the partitioning column itself.
If the table has no foreign keys and the only UNIQUE index is based on an IDENTITY column, then partitioning gets a whole lot easier but I still like the idea of Michaels suggestion for this particular problem much better.
--Jeff Moden
Change is inevitable... Change for the better is not.