分区表和分区切换应用 SlidingWindow

--**************************************************************************************
-- Date:    07/19/2005
--
-- File:    sliding.sql for Sliding Window Example
--
-- Summary: Managing a Range Partitioned Table 
--            Archive transaction data for September 2003.
--
--**************************************************************************************
--
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation.  All rights reserved.
--
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation.  See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
-- KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
-- IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--
--**************************************************************************************

SET DATEFORMAT mdy;
GO

USE [AdventureWorks];
GO

ALTER PARTITION SCHEME TransactionsPS1 
NEXT USED [PRIMARY];
GO

-- Add a new partition on the end of table TransactionHistory for August 2004.
ALTER PARTITION FUNCTION TransactionRangePF1() 
SPLIT RANGE ('9/01/2004');
GO

ALTER PARTITION SCHEME TransactionArchivePS2 
NEXT USED [PRIMARY];
GO

-- Add a new partition to table TransactionHistoryArchive to hold the 
-- September 2003 data.
ALTER PARTITION FUNCTION TransactionArchivePF2() 
SPLIT RANGE ('10/01/2003');
GO

-- You must add a check constraint to table TransactionHistory to define 
-- the boundary for the first partition before switching it out.
ALTER TABLE [Production].[TransactionHistory] 
ADD CONSTRAINT [CK_TransactionHistory_DateRange] 
CHECK ([TransactionDate] >= '9/01/2003');
GO

-- Move the data for September 2003 from table TransactionHistory to 
-- table TransactionHistoryArchive.
ALTER TABLE [Production].[TransactionHistory] 
SWITCH PARTITION 1 
TO [Production].[TransactionHistoryArchive] PARTITION 2;
GO

-- Merge the first two partitions of table TransactionHistory.
ALTER PARTITION FUNCTION TransactionRangePF1() 
MERGE RANGE ('10/01/2003');
GO

-- Merge the September 2003 partition of table TransactionHistoryArchive 
-- with the first partition.
ALTER PARTITION FUNCTION TransactionArchivePF2() 
MERGE RANGE ('9/01/2003');
GO

-- Remove the date constraint on table TransactionHistory.
ALTER TABLE [Production].[TransactionHistory] 
DROP CONSTRAINT [CK_TransactionHistory_DateRange];
GO

SELECT OBJECT_NAME([object_id]), * FROM [sys].[partitions]
WHERE [object_id] = OBJECT_ID('[Production].[TransactionHistory]') 
ORDER BY [partition_number], [index_id];
GO

SELECT OBJECT_NAME([object_id]), * FROM [sys].[partitions]
WHERE [object_id] = OBJECT_ID('[Production].[TransactionHistoryArchive]') 
ORDER BY [partition_number], [index_id];
GO

USE [master];
GO