DECLARE
@dt datetime
SET
@dt =
'20020101'
CREATE
PARTITION
FUNCTION
PF_HistoryArchive(datetime)
AS
RANGE
RIGHT
FOR
VALUES
(
@dt,
DATEADD(
Year
, 1, @dt))
SET
@dt =
'20000101'
CREATE
PARTITION
FUNCTION
PF_History(datetime)
AS
RANGE
RIGHT
FOR
VALUES
(
@dt,
DATEADD(
Month
, 1, @dt),
DATEADD(
Month
, 2, @dt),
DATEADD(
Month
, 3, @dt),
DATEADD(
Month
, 4, @dt),
DATEADD(
Month
, 5, @dt),
DATEADD(
Month
, 6, @dt),
DATEADD(
Month
, 7, @dt),
DATEADD(
Month
, 8, @dt),
DATEADD(
Month
, 9, @dt),
DATEADD(
Month
, 10, @dt),
DATEADD(
Month
, 11, @dt),
DATEADD(
Month
, 12, @dt))
GO
CREATE
PARTITION SCHEME PS_HistoryArchive
AS
PARTITION PF_HistoryArchive
TO
([
PRIMARY
], [
PRIMARY
], [
PRIMARY
])
CREATE
PARTITION SCHEME PS_History
AS
PARTITION PF_History
TO
([
PRIMARY
], [
PRIMARY
],
[
PRIMARY
], [
PRIMARY
], [
PRIMARY
],
[
PRIMARY
], [
PRIMARY
], [
PRIMARY
],
[
PRIMARY
], [
PRIMARY
], [
PRIMARY
],
[
PRIMARY
], [
PRIMARY
], [
PRIMARY
])
GO
DROP
INDEX
Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP
INDEX
Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
DROP
INDEX
Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP
INDEX
Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO
ALTER
TABLE
Production.TransactionHistoryArchive
DROP
CONSTRAINT
PK_TransactionHistoryArchive_TransactionID
WITH
(
MOVE
TO
PS_HistoryArchive(TransactionDate))
ALTER
TABLE
Production.TransactionHistory
DROP
CONSTRAINT
PK_TransactionHistory_TransactionID
WITH
(
MOVE
TO
PS_History(TransactionDate))
GO
ALTER
TABLE
Production.TransactionHistoryArchive
ADD
CONSTRAINT
PK_TransactionHistoryArchive_TransactionID
PRIMARY
KEY
CLUSTERED(
TransactionID,
TransactionDate)
ALTER
TABLE
Production.TransactionHistory
ADD
CONSTRAINT
PK_TransactionHistory_TransactionID
PRIMARY
KEY
CLUSTERED(
TransactionID,
TransactionDate)
GO
CREATE
INDEX
IX_TransactionHistoryArchive_ProductID
ON
Production.TransactionHistoryArchive(
ProductID)
CREATE
INDEX
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ON
Production.TransactionHistoryArchive(
ReferenceOrderID,
ReferenceOrderLineID)
CREATE
INDEX
IX_TransactionHistory_ProductID
ON
Production.TransactionHistory(
ProductID)
CREATE
INDEX
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ON
Production.TransactionHistory(
ReferenceOrderID,
ReferenceOrderLineID)
GO
SELECT
SchemaName = S.
name
,
TableName = TB.
name
,
PartitionScheme = PS.
name
,
PartitionFunction = PF.
name
,
PartitionFunctionRangeType =
CASE
WHEN
boundary_value_on_right = 0
THEN
'LEFT'
ELSE
'RIGHT'
END
,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM
sys.schemas S
INNER
JOIN
sys.tables TB
ON
S.schema_id = TB.schema_id
INNER
JOIN
sys.indexes IDX
on
TB.object_id = IDX.object_id
AND
IDX.index_id < 2
INNER
JOIN
sys.partition_schemes PS
ON
PS.data_space_id = IDX.data_space_id
INNER
JOIN
sys.partition_functions PF
ON
PS.function_id = PF.function_id
GO
ALTER
PARTITION SCHEME PS_HistoryArchive
NEXT
USED [
PRIMARY
]
DECLARE
@dt datetime
SET
@dt =
'20030901'
ALTER
PARTITION
FUNCTION
PF_HistoryArchive()
SPLIT RANGE(@dt)
ALTER
TABLE
Production.TransactionHistory
SWITCH PARTITION 2
TO
Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)
ALTER
PARTITION
FUNCTION
PF_HistoryArchive()
MERGE RANGE(@dt)
GO
DECLARE
@dt datetime
SET
@dt =
'20030901'
ALTER
PARTITION
FUNCTION
PF_History()
MERGE RANGE(@dt)
ALTER
PARTITION SCHEME PS_History
NEXT
USED [
PRIMARY
]
SET
@dt =
'20041001'
ALTER
PARTITION
FUNCTION
PF_History()
SPLIT RANGE(@dt)
GO
CREATE
TABLE
Production.TransactionHistoryArchive_2001_temp(
TransactionID
int
NOT
NULL
,
ProductID
int
NOT
NULL
,
ReferenceOrderID
int
NOT
NULL
,
ReferenceOrderLineID
int
NOT
NULL
DEFAULT
((0)),
TransactionDate datetime
NOT
NULL
DEFAULT
(GETDATE()),
TransactionType
nchar
(1)
NOT
NULL
,
Quantity
int
NOT
NULL
,
ActualCost money
NOT
NULL
,
ModifiedDate datetime
NOT
NULL
DEFAULT
(GETDATE()),
CONSTRAINT
PK_TransactionHistoryArchive_2001_temp_TransactionID
PRIMARY
KEY
CLUSTERED(
TransactionID,
TransactionDate)
)
ALTER
TABLE
Production.TransactionHistoryArchive
SWITCH PARTITION 1
TO
Production.TransactionHistoryArchive_2001_temp
DECLARE
@dt datetime
SET
@dt =
'20020101'
ALTER
PARTITION
FUNCTION
PF_HistoryArchive()
MERGE RANGE(@dt)
ALTER
PARTITION SCHEME PS_HistoryArchive
NEXT
USED [
PRIMARY
]
SET
@dt =
'20040101'
ALTER
PARTITION
FUNCTION
PF_HistoryArchive()
SPLIT RANGE(@dt)