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