Particionamiento horizontal

ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgFebrero2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgMarzo2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgAbril2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgMayo2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgJunio2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgJulio2016
GO
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartEnero2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontal.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgEnero2016]
ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartFebrero2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalFeb.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgFebrero2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartMarzo2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalMar.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgMarzo2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartAbril2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalAbr.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgAbril2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartMayo2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalMay.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgMayo2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartJunio2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalJun.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgJUnio2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartJulio2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalJul.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgJulio2016]


SELECT
name as [FileName],
physical_name as [FilePath]
FROM sys.database_files
where type_desc = 'ROWS'
GO

CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)
AS RANGE RIGHT FOR VALUES ('20160101', '20160201', '20160301', '20160401',
'20160501', '20160601');

DROP PARTITION FUNCTION PartitioningByMonth


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')



CREATE PARTITION SCHEME PartitionBymonth
AS PARTITION PartitioningByMonth
TO (fgEnero2016, fgFebrero2016, fgMarzo2016,
    fgAbril2016,fgMayo2016,fgJunio2016, fgJulio2016);


CREATE TABLE Reports
(ReportDate datetime PRIMARY KEY,
MonthlyReport varchar(max))
ON PartitionBymonth (ReportDate);
GO

INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20150105', 'ReportJanuary' UNION ALL
SELECT '20160205', 'ReportFebryary' UNION ALL
SELECT '20160308', 'ReportMarch' UNION ALL
SELECT '20160409', 'ReportApril' UNION ALL
SELECT '20160509', 'ReportMay' UNION ALL
SELECT '20160609', 'ReportJune' UNION ALL
SELECT '20160709', 'ReportJuly'

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'


SELECT * FROM Reports
WHERE ReportDate>'20160303'

DELETE FROM Reports