Out of curiosity is this database for an application that allows users to create/define custom fields or even data types? The only reason I'm asking is because I've had...

Because that is the only table that was causing performance issues when I broke down...

I have to control over that query...I'm not any happier about it either.

...and yes it is dynamically created.

Attached requested files...

That's a very good point...I appreciate all the input. I'll carefully consider both options and test them out.

Thanks

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...

Well I think I might have it conceptually. Just starting to code it right now...I'll post the final result if everything checks out.

Just create a partition function on two...

Sorry one more question if I may.

What would be the best stategy for creating a new partition on the main table in such a way that I would not have...

Even better...that's fantastic Gila.

Thank You

Hmmmm...just quickly reading over msdn articles on switching it seems you can only switch a partition with an empty one. So then I would see it as a three...

GilaMonster (6/21/2012)

---

This actually sounds like a case for partitioning. Partition the main table, make sure the staging table is identical (indexes, constraints, columns, etc) then to move the data from...

Yes, I am using Enterprise edition. Partitioning is not something I have yet experimented with but that sounds like a great solution. Thanks for the idea.