Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan.
Vertical Partitioning on SQL Server tables
Vertical table partitioning is mostly used to increase SQL Server performance especially in cases where a query retrieves all columns from a table that contains a number of very wide text or BLOB columns. In this case to reduce access times the BLOB columns can be split to its own table. Another example is to restrict access to sensitive data e.g. passwords, salary information etc. Vertical partitioning splits a table into two or more tables containing different columns:
Example:
Horizontal partitioning divides a table into multiple tables that contain the same number of columns, but fewer rows. For example, if a table contains a large number of rows that represent monthly reports it could be partitioned horizontally into tables by years, with each table representing all monthly reports for a specific year.
Partitioning column is usually a datetime column but all data types that are valid for use as index columns can be used as a partitioning column, except a timestamp column. The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max), Microsoft .NET Framework common language runtime (CLR) user-defined type, and alias data type columns cannot be specified.
Filegroups: A filegroup is a logical storage unit. Every database has a primary filegroup that contains the primary data file (.mdf). An additional, user-defined, filegroups can be created to contain secondary files (.ndf). We will create 12 filegroups for every month:
Step 1
ADD FILEGROUP January
GO
ALTER DATABASE youtube
ADD FILEGROUP February
GO
ALTER DATABASE youtube
ADD FILEGROUP March
GO
ALTER DATABASE youtube
ADD FILEGROUP April
GO
ALTER DATABASE youtube
ADD FILEGROUP May
GO
ALTER DATABASE youtube
ADD FILEGROUP June
GO
ALTER DATABASE youtube
ADD FILEGROUP July
GO
ALTER DATABASE youtube
ADD FILEGROUP Avgust
GO
ALTER DATABASE youtube
ADD FILEGROUP September
GO
ALTER DATABASE youtube
ADD FILEGROUP October
GO
ALTER DATABASE youtube
ADD FILEGROUP November
GO
ALTER DATABASE youtube
ADD FILEGROUP December
GO
step 2
FROM sys.filegroups
WHERE type = 'FG'
step 3
ALTER DATABASE [youtube]
ADD FILE
(
NAME = [PartJan],
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\youtube.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [January]
ALTER DATABASE [youtube]
ADD FILE
(
NAME = [PartFeb],
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\youtube1.ndf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [February]
Repeat same for all group
name as [FileName],
physical_name as [FilePath]
FROM sys.database_files
where type_desc = 'ROWS'
GO
Partition function: A partition function is a function that maps the rows of a partitioned table into partitions based on the values of a partitioning column.
AS RANGE RIGHT FOR VALUES ('20140201', '20140301', '20140401',
'20140501', '20140601', '20140701', '20140801',
'20140901', '20141001', '20141101', '20141201');
Create Partition Schema:
AS PARTITION PartitioningBymonth
TO (January, February, March,
April, May, June, July,
Avgust, September, October,
November, December);
Lets Create Table using PartitionbyMonth Schema:
(ReportDate datetime PRIMARY KEY,
MonthlyReport varchar(max))
ON PartitionBymonth (ReportDate);
SELECT '20140105', 'ReportJanuary' UNION ALL
SELECT '20140205', 'ReportFebryary'
Lets verify the row in different partition: