分区表和分区切换应用 SlidingWindow

分区表和分区切换应用 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

posted on 2011-07-06 15:19  Fanr_Zh 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Amaranthus/archive/2011/07/06/2099250.html