What is horizontal partitioning in SQL Server?

SQL Server Horizontal Partitioning  separates a table into multiple tables , all with the same  number of columns, but with fewer rows. A typical example is where you might have data - used for monthly charts -  separated monthly and each partitioned table represents a month.   

Decide on a column to use for separating the data. An example column may be a date column.  

This example code represents horizontal partitioning by creating a new new partitioned table. 

--create test DB
create database HorzPartitioining

--create additional filegroups to store the secondary files
use HorzPartitioining
ALTER DATABASE HorzPartitioining
ADD FILEGROUP January
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP February
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP March
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP April
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP May
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP June
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP July
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP August
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP September
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP October
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP November
GO
ALTER DATABASE HorzPartitioining
ADD FILEGROUP December
GO
--check created filegroups
use HorzPartitioining
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'

--Add the a secondary file to every filegroups
ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartJan],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [January]
 
 ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartFeb],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining2.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [February]
  ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartMar],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining3.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [March]
  ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartApr],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining4.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [April]
  ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartMay],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining5.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [May]
  ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartJun],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining6.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [June]
  ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartJul],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining7.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [July]
   ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartAug],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining8.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [August]
   ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartSep],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining9.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [September]
 
    ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartOct],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining10.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [October]

   ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartNov],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining11.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [November]
    ALTER DATABASE [HorzPartitioining]
    ADD FILE 
    (
    NAME = [PartDec],
    FILENAME = 'd:\mssql15.sqldb\mssql\data\HorzPartitioining12.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [December]
 

 --create partition function - mapping rows of a partitioned table into partitions based on a partitioning column
 CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)
AS RANGE RIGHT FOR VALUES ('20190201', '20190301', '20190401',
               '20190501', '20190601', '20190701', '20190801', 
               '20190901', '20191001', '20191101', '20191201');

 --create partition scheme - Map the partitions of a Partitioned table to filegroups -> to determine the domain of the partitions
 CREATE PARTITION SCHEME PartitionBymonth
AS PARTITION PartitioningBymonth
TO (January, February, March, 
    April, May, June, July, 
    August, September, October, 
    November, December);

--Create table using the PartitionByScheme just created 
CREATE TABLE Charts
(ChartDate datetime PRIMARY KEY,
MonthlyChart varchar(max))
ON PartitionBymonth (ChartDate);
GO
--Insert some Data into the Chart Table 
INSERT INTO Charts (ChartDate,MonthlyChart)
SELECT '20190105', 'ChartJanuary' UNION ALL
SELECT '20190205', 'ChartFebryary' UNION ALL
SELECT '20190308', 'ChartMarch' UNION ALL
SELECT '20190409', 'ChartApril' UNION ALL
SELECT '20190509', 'ChartMay' UNION ALL
SELECT '20190609', 'ChartJune' UNION ALL
SELECT '20190709', 'ChartJuly' UNION ALL
SELECT '20190809', 'ChartAugust' UNION ALL
SELECT '20190909', 'ChartSeptember' UNION ALL
SELECT '20191009', 'ChartOctober' UNION ALL
SELECT '20191109', 'ChartNovember' UNION ALL
SELECT '20191209', 'ChartDecember'

--check to see the rows per partition
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) = 'Charts'

Read More on various other methods of separating data in SQL Server

SQL Server scaling techniques

Predictability is the key to scalability

Premature optimization - SQL Antipattern #008