分區表和分區切換應用 SlidingWindow

<a></a>

--**************************************************************************************

-- 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];

ALTER PARTITION SCHEME TransactionsPS1

NEXT USED [PRIMARY];

-- Add a new partition on the end of table TransactionHistory for August 2004.

ALTER PARTITION FUNCTION TransactionRangePF1()

SPLIT RANGE ('9/01/2004');

ALTER PARTITION SCHEME TransactionArchivePS2

-- Add a new partition to table TransactionHistoryArchive to hold the

-- September 2003 data.

ALTER PARTITION FUNCTION TransactionArchivePF2()

SPLIT RANGE ('10/01/2003');

-- 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] &gt;= '9/01/2003');

-- Move the data for September 2003 from table TransactionHistory to

-- table TransactionHistoryArchive.

SWITCH PARTITION 1

TO [Production].[TransactionHistoryArchive] PARTITION 2;

-- Merge the first two partitions of table TransactionHistory.

MERGE RANGE ('10/01/2003');

-- Merge the September 2003 partition of table TransactionHistoryArchive

-- with the first partition.

MERGE RANGE ('9/01/2003');

-- Remove the date constraint on table TransactionHistory.

DROP CONSTRAINT [CK_TransactionHistory_DateRange];

SELECT OBJECT_NAME([object_id]), * FROM [sys].[partitions]

WHERE [object_id] = OBJECT_ID('[Production].[TransactionHistory]')

ORDER BY [partition_number], [index_id];

WHERE [object_id] = OBJECT_ID('[Production].[TransactionHistoryArchive]')

USE [master];