Partitioning Example Part III (With Filegroups)

--- Creating a demo database with filegroups to store associated data on respective filegroups
CREATE DATABASE SalesDB ON PRIMARY
( NAME = Primary_Data, FILENAME = 'C:\Sales_Data.mdf',
 SIZE = 100, MAXSIZE = 150, FILEGROWTH = 5 ),
 FileGroup Sales_2010 ( NAME = Sales2010, FILENAME = 'D:\Sales2010.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),
 FileGroup Sales_2011   ( NAME = Sales2011,   FILENAME = 'E:\Sales2011.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),
 FileGroup Sales_2012 ( NAME = Sales2012, FILENAME = 'F:\Sales2012.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )

--- Verify file groups are created properly
USE SalesDB
SELECT * FROM SalesDB..sysfiles

--- Creating Partition function to store 3 years of data on it
CREATE PARTITION FUNCTION SalesData_Function (DATETIME)
AS RANGE LEFT FOR VALUES ('12/31/2010','12/31/2011','12/31/2012')  -- FOUR PARTITIONS

--- Creating a partition scheme to store data on respective filegroups
CREATE PARTITION SCHEME SalesData_Scheme AS PARTITION SalesData_Function TO (Sales_2010, Sales_2011, Sales_2012, [PRIMARY])

-- Creating a sales demo partitioned table on order_date column
Create table Sales_Data (Order_ID int, OrderName varchar(15), Order_City varchar(30), Order_Date DATETIME, Order_Amt INT) ON SalesData_Scheme(Order_Date)

--- Insert demo data
insert into Sales_Data values(1015,'order01','Bangalore','20-Jul-2009', 100)
insert into Sales_Data values(1001,'order1','Hyderabad','12-Jan-2010', 100)
insert into Sales_Data values(1002,'order2','Chennai','06-Sep-2010', 100)
insert into Sales_Data values(1003,'order3','Bangalore','11-Nov-2010', 100)
insert into Sales_Data values(1004,'order4','Hyderabad','11-Feb-2011', 100)
insert into Sales_Data values(1005,'order5','Chennai','04-Mar-2011', 100)
insert into Sales_Data values(1006,'order6','Bangalore','09-Jul-2011', 100)
insert into Sales_Data values(1008,'order7','Chennai','15-Aug-2011', 100)
insert into Sales_Data values(1009,'order8','Hyderabad','20-Dec-2011', 100)
insert into Sales_Data values(1010,'order9','Bangalore','19-Jan-2012', 100)
insert into Sales_Data values(1011,'order10','Bangalore','14-Apr-2012', 100)
insert into Sales_Data values(1012,'order11','Hyderabad','24-Dec-2012', 100)
insert into Sales_Data values(1013,'order12','Chennai','1-Jan-2013', 100)
insert into Sales_Data values(1014,'order13','Hyderabad','09-Jun-2013', 100)
insert into Sales_Data values(1015,'order14','Bangalore','20-Jul-2012', 100)

SELECT * FROM Sales_Data  -- Verify records

SELECT $Partition.SalesData_Function(Order_Date) AS [Partition],* from Sales_Data ORDER BY [Partition] -- Verify date by partitioned column

--- Verify number of rows on each partitions along with filegroup name and partition max value
SELECT DISTINCT OBJECT_NAME(si.object_id) Table_Name, p.partition_number,fg.name,
p.rows, (SELECT value FROM sys.partition_range_values prv WHERE prv.boundary_id = p.partition_number) Partition_Value
FROM sys.partitions p, sys.indexes si, sys.partition_schemes ps,
sys.destination_data_spaces dds, sys.filegroups fg, sys.partition_functions pf
WHERE p.object_id = si.object_id and p.index_id = si.index_id AND si.data_space_id = ps.data_space_id AND dds.partition_scheme_id = ps.data_space_id
AND fg.data_space_id = dds.data_space_id AND p.partition_number = dds.destination_id AND pf.function_id = ps.function_id