--Add File groups
ALTER DATABASE Db_Partition
ADD FILEGROUP January
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP February
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP March
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP April
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP May
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP June
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP July
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP Avgust
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP September
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP October
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP November
GO
ALTER DATABASE Db_Partition
ADD FILEGROUP December
GO
--get file group's List
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'
--Add Files Script
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartJan],
FILENAME = 'E:\Test\DATA\PartitioningDB.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [January]
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartFebruary],
FILENAME = 'E:\Test\DATA\PartitioningDB_February.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [February]
GO
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartMarch],
FILENAME = 'E:\Test\DATA\PartitioningDB_March.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [March]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartApril],
FILENAME = 'E:\Test\DATA\PartitioningDB_April.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [April]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartMay],
FILENAME = 'E:\Test\DATA\PartitioningDB_May.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [May]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartJune],
FILENAME = 'E:\Test\DATA\PartitioningDB_June.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [June]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartJuly],
FILENAME = 'E:\Test\DATA\PartitioningDB_July.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [July]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartAvgust],
FILENAME = 'E:\Test\DATA\PartitioningDB_Avgust.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [Avgust]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartSeptember],
FILENAME = 'E:\Test\DATA\PartitioningDB_September.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [September]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartOctober],
FILENAME = 'E:\Test\DATA\PartitioningDB_October.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [October]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartNovember],
FILENAME = 'E:\Test\DATA\PartitioningDB_November.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [November]
Go
ALTER DATABASE [Db_Partition]
ADD FILE
(
NAME = [PartDecember],
FILENAME = 'E:\Test\DATA\PartitioningDB_December.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [December]
--To check files created added to the filegroups run the following query:
SELECT
name as [FileName],
physical_name as [FilePath]
FROM sys.database_files
where type_desc = 'ROWS'
GO
--CREATE PARTITION FUNCTION
CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)
AS RANGE RIGHT FOR VALUES ('20140201', '20140301', '20140401',
'20140501', '20140601', '20140701', '20140801',
'20140901', '20141001', '20141101', '20141201');
--CREATE PARTITION SCHEME
CREATE PARTITION SCHEME PartitionBymonth
AS PARTITION PartitioningBymonth
TO (January, February, March,
April, May, June, July,
Avgust, September, October,
November, December);
--Now we’re going to create the table using the PartitionBymonth partition scheme, and fill it with the test data:
CREATE TABLE Reports
(ReportDate datetime PRIMARY KEY,
MonthlyReport varchar(max))
ON PartitionBymonth (ReportDate);
GO
INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20160105', 'ReportJanuary' UNION ALL
SELECT '20140205', 'ReportFebryary' UNION ALL
SELECT '20140308', 'ReportMarch' UNION ALL
SELECT '20140409', 'ReportApril' UNION ALL
SELECT '20140509', 'ReportMay' UNION ALL
SELECT '20140609', 'ReportJune' UNION ALL
SELECT '20140709', 'ReportJuly' UNION ALL
SELECT '20140809', 'ReportAugust' UNION ALL
SELECT '20140909', 'ReportSeptember' UNION ALL
SELECT '20141009', 'ReportOctober' UNION ALL
SELECT '20141109', 'ReportNovember' UNION ALL
SELECT '20141209', 'ReportDecember'
Select * from Reports
---
SELECT
p.partition_number AS PartitionNumber,
f.name AS PartitionFilegroup,
p.rows AS NumberOfRows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'Reports'
