sliding window scenario - taking partitions offline

Is there a way in MS SQL Server 2005 to take a partition / file groups / files offline?

I have a lot of data in certain tables and would like to use the sliding window Scenario: http://开发者_高级运维msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx#sql2k5parti_topic24

Instead of keeping all the data in the first partition, I would like to take the partition (or files or file group if possible) offline and make it unavailable for my queries.

---

The best description of the sliding window scenario I have ever seen is over here:

http://blogs.msdn.com/b/hanspo/archive/2009/08/21/inside-of-table-and-index-partitioning-in-microsoft-sql-server.aspx

The article title is "Microsoft SQL Server Inside Out. Inside of Table and Index Partitioning in Microsoft SQL Server".

I do not see the reason to retell this article here. Just go, read and exercise with example by your hands.

And one more very good example is here: http://sqlserverpedia.com/wiki/Example_of_Creating_Partitioned_Tables

These two articles should be enough to get your table partitioned even you deal with partitioning at very first time.

Good luck.

---

You can start from here: How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005.

But i would recommend against putting old partitions offline, a much better approach is to have two tables with identical structure, Current and Archive, and use the sliding widnow partitioning to move the old partitions from Current to Archive, see Transferring Data Efficiently by Using Partition Switching.