-- Create Partition Function, Scheme and Table
CREATE PARTITION FUNCTION PF_3MonthRolling(INT)
AS RANGE RIGHT FOR VALUES 
(
	20070201,
	20070301
)
GO

CREATE PARTITION SCHEME PS_3MonthRolling
AS PARTITION PF_3MonthRolling
TO (FG1, FG2, FG3)
GO

CREATE TABLE MyFact
(
	ID INT,
	DateNumber INT,
	ItemCount INT
) 
ON PS_3MonthRolling(DateNumber)
GO

CREATE CLUSTERED INDEX IX_MyFact_DateNumber ON MyFact
(
	DateNumber ASC
) WITH FILLFACTOR = 100 
ON PS_3MonthRolling(DateNumber)
GO

-- Load Sample Data
INSERT MyFact
SELECT 1, 20070114, 12
UNION ALL SELECT 2, 20070118, 32
UNION ALL SELECT 3, 20070205, 18
UNION ALL SELECT 4, 20070211, 98
UNION ALL SELECT 5, 20070227, 13
UNION ALL SELECT 6, 20070303, 76
UNION ALL SELECT 7, 20070309, 11
UNION ALL SELECT 8, 20070314, 1
UNION ALL SELECT 9, 20070323, 3


-- Verify that data was split into the appropriate partitions
SELECT 
	$PARTITION.PF_3MonthRolling(DateNumber) AS [Partition#],
	COUNT(*) AS RowCnt, 
	MIN(DateNumber) AS MinDate, 
	MAX(DateNumber) AS MaxDate
FROM 
	MyFact 
GROUP BY 
	$PARTITION.PF_3MonthRolling(DateNumber)
ORDER BY 
	[Partition#]

-- DROP TABLE MyFact20070201
-- Create New Table to move OLD data into
CREATE TABLE MyFact20070201
(
	ID INT,
	DateNumber INT,
	ItemCount INT
) 
ON FG1
GO

CREATE CLUSTERED INDEX IX_MyFact20070201_DateNumber ON MyFact20070201
(
	DateNumber ASC
) WITH FILLFACTOR = 100 
ON FG1

ALTER PARTITION SCHEME PS_3MonthRolling 
	NEXT USED [FG2]
GO


-- Move old data to "OLD" table
ALTER TABLE dbo.MyFact
SWITCH PARTITION 1 
TO dbo.MyFact20070201
GO

-- Merge the Range -- This is the problem (at least the way I've done it)
ALTER PARTITION FUNCTION PF_3MonthRolling()
	MERGE RANGE (20070201)
GO

-- Check partitions (this should be as expected) 2 partitions with data
SELECT 
	$PARTITION.PF_3MonthRolling(DateNumber) AS [Partition#],
	COUNT(*) AS RowCnt, 
	MIN(DateNumber) AS MinDate, 
	MAX(DateNumber) AS MaxDate
FROM 
	MyFact 
GROUP BY 
	$PARTITION.PF_3MonthRolling(DateNumber)
ORDER BY 
	[Partition#]

------------------------------------------------------------------------------
-- HERE IS THE PROBLEM
------------------------------------------------------------------------------
-- When we check the partions and their file groups we see
-- that the data on our original partition 2 (FG2) has moved over to FG1.
SELECT
	ps.name AS PSName, 
	dds.destination_id AS PartitionNumber, 
	fg.name AS FileGroupName
FROM 
	sys.tables AS t 
INNER JOIN sys.indexes AS i 
  ON t.object_id = i.object_id
INNER JOIN sys.partition_schemes AS ps 
  ON i.data_space_id = ps.data_space_id
INNER JOIN sys.destination_data_spaces AS dds 
  ON ps.data_space_id = dds.partition_scheme_id
INNER JOIN sys.filegroups AS fg
      ON dds.data_space_id = fg.data_space_id
WHERE 
	t.name = 'MyFact' 
	AND i.index_id IN (0,1) 



-- Create New table to merge in
CREATE TABLE MyFact20070401
(
	ID INT,
	DateNumber INT,
	ItemCount INT
) 
ON FG1
GO

CREATE CLUSTERED INDEX IX_MyFact20070401_DateNumber ON MyFact20070401
(
	DateNumber ASC
) WITH FILLFACTOR = 100 
ON FG1


-- Split the Range for new data
ALTER PARTITION FUNCTION PS_3MonthRolling()
	SPLIT RANGE (20070401)
GO

--DROP TABLE MyFact
--DROP PARTITION SCHEME PS_3MonthRolling
--DROP PARTITION FUNCTION PF_3MonthRolling