sql-table-partioning

table-partitioning.sql

ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP January
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP February
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP March
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP April
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP May
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP June
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP July
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP August
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP September
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP October
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP November
GO
ALTER DATABASE [ConfigurationDb_Staging]
ADD FILEGROUP December
GO



SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'




ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartJan],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging1.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [January]



ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartFeb],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging2.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [February]


ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartMar],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging3.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [March]


ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartApr],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging4.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [April]


	
ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartMay],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging5.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [May]


	
ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartJun],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging6.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [June]


	
ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartJul],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging7.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [July]



ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartAug],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging8.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [August]


ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartSep],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging9.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [September]


	
ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartOct],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging10.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [October]


ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartNov],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging11.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [November]

ALTER DATABASE [ConfigurationDb_Staging]
    ADD FILE 
    (
    NAME = [PartDec],
    FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ConfigurationDb_Staging12.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [December]



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 ('20190201', '20190301', '20190401',
               '20190501', '20190601', '20190701', '20190801', 
               '20190901', '20191001', '20191101', '20191201');


CREATE PARTITION SCHEME PartitionBymonth
AS PARTITION PartitioningBymonth
TO (January, February, March, 
    April, May, June, July, 
    August, September, October, 
    November, December);


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