I'm trying to use partitioning to switch data in and out of a partition on an hourly basis. I have a large amount of data that I want to switch quickly, instead of updating/inserting/deleting rows based on an update to a source system.
Is partitioning the best route, so far I've tried SQL 2000 , and it looks like attempting to do an ALTER VIEW I'll get table locks occurring if there is set of heavy queries running n the data already. I've tried 2005 but it looks like I have a similar problem.
Ideally I want to query one table, switch a load of data in and out, and not impact any SELECT queries running on the old data. Have you come across anything like this... I get the feeling I may need to queue the queries in some way, or prevent users from querying the database when the switch is to occur, as the ALTER TABLE/VIEW is practically instant.