Welcome back for part 2 of my SQL Snack Pack on Table Partitioning! If you have not watched the first video, I would highly encourage you to do so.

In this second video I will discuss the following:
Creating a new Range in our Partition for new data (SPLIT RANGE)
Merging an older Range of data for archiving (MERGE RANGE)
Reviewing detailed metadata on partitions
Removing old Filegroups and Data files no longer needed after archiving
Using the SWITCH PARTITION command to move data between partitions (Archiving, Moving data from Staging to Production tables in DW environment)

Code:

--Partitioning 102
--Let us put the 2015 data into a new partition
Use PartitionTest

ALTER PARTITION FUNCTION partFn_Date()
SPLIT RANGE ('2015-01-01');

SELECT *,$Partition.partFn_Date(ORDERDate)
FROM Orders

--Remove 2013 Partition using a Merge
ALTER PARTITION FUNCTION partFn_Date ()
MERGE RANGE ('2013-01-01');

--
--Query to determine table filegroup by index and partition
--Script provided by Jason Strate
--http://www.jasonstrate.com/2013/01/determining-file-group-for-a-table/
--
USE PartitionTest
Go
SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
,t.name AS table_name
,i.index_id
,i.name AS index_name
,p.partition_number
,fg.name AS filegroup_name
,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id AND p.partition_number=dds.destination_id
INNER JOIN sys.filegroups fg ON COALESCE(dds.data_space_id, i.data_space_id)=fg.data_space_id

---Remove the file and filegroup you no longer need
USE master
GO
ALTER DATABASE PartitionTest
REMOVE FILE FG2013
GO

USE master
GO
ALTER DATABASE PartitionTest
REMOVE FILEGROUP FG2013
GO

USE PartitionTest
--Switch Data into another table then truncate
Create Table OrdersArchive
(
OrderID Int NOT NULL,
OrderDesc Varchar(50),
OrderDate datetime,
OrderAmount money
)
ON FGArchive
GO

ALTER TABLE Orders SWITCH Partition 1 to OrdersArchive

SELECT *
FROM OrdersArchive

SELECT *
FROM Orders