Partitioning in sql server database

 
--// SQL Partition
--=============================================================
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP January
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP February
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP March
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP April
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP May
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP June
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP July
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP August
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP September
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP October
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP November
GO
ALTER DATABASE AF_CTS_T1_H
ADD FILEGROUP December
GO
 
 
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartJan],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Jan.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [January]
 
GO
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartFeb],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Feb.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [February]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartMar],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Mar.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [March]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartApr],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Apr.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [April]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartMay],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_May.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [May]
 
GO
 
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartJun],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Jun.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [June]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartJul],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Jul.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [July]
 
GO
 
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartAug],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Aug.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [August]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartSep],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Sep.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [September]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartOct],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Oct.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [October]
 
GO
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartNov],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Nov.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [November]
 
GO
 
 
 
ALTER DATABASE AF_CTS_T1_H
    ADD FILE 
    (
    NAME = [PartDec],
    FILENAME = 'C:\SQL\Data\AF_CTS_T12_H_Dec.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [December]
 
GO
 
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 ('20190101','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);
 
 
CREATE TABLE Reports
(ReportDate datetime PRIMARY KEY,
MonthlyReport varchar(max))
ON PartitionBymonth (ReportDate);
GO
 
INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20190105', 'ReportJanuary' UNION ALL
SELECT '20190205', 'ReportFebryary' UNION ALL
SELECT '20190308', 'ReportMarch' UNION ALL
SELECT '20190409', 'ReportApril' UNION ALL
SELECT '20190509', 'ReportMay' UNION ALL
SELECT '20190609', 'ReportJune' UNION ALL
SELECT '20190709', 'ReportJuly' UNION ALL
SELECT '20190809', 'ReportAugust' UNION ALL
SELECT '20190909', 'ReportSeptember' UNION ALL
SELECT '20191009', 'ReportOctober' UNION ALL
SELECT '20191109', 'ReportNovember' UNION ALL
SELECT '20191209', 'ReportDecember'
 
 
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'
 
 
 
INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20190106', 'ReportJanuary' UNION ALL
SELECT '20190206', 'ReportFebryary' UNION ALL
SELECT '20190306', 'ReportMarch' UNION ALL
SELECT '20190406', 'ReportApril' UNION ALL
SELECT '20190506', 'ReportMay' UNION ALL
SELECT '20190606', 'ReportJune' UNION ALL
SELECT '20190706', 'ReportJuly' UNION ALL
SELECT '20190806', 'ReportAugust' UNION ALL
SELECT '20190906', 'ReportSeptember' UNION ALL
SELECT '20191006', 'ReportOctober' UNION ALL
SELECT '20191106', 'ReportNovember' UNION ALL
SELECT '20191206', 'ReportDecember'
 
 
 
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'

Crie um site com

  • Totalmente GRÁTIS
  • Design profissional
  • Criação super fácil

Este site foi criado com Webnode. Crie o seu de graça agora!