Data Partitioning Deep Dive: From SQL Server and Azure SQL to Microsoft Fabric
Partitioning is a key technique for managing and optimizing large datasets, but its implementation differs across data platforms. In this post, we explore partitioning strategies across SQL Server, Azure SQL and Microsoft Fabric, including key concepts, implementation approaches, benefits and important considerations
Partitioning large tables offers several advantages. It can speed up data loading and archiving considerably, it allows maintenance to be carried out on individual partitions instead of the whole table, and in some scenarios, it can improve query performance.
Partitioning is usually seen as a DBA’s responsibility, but I’d strongly encourage BI developers to build a good working knowledge of it too. Many smaller organizations don’t have a dedicated DBA, so BI developers often end up handling these tasks themselves. Understanding partitioning also puts you in a better position to optimize the performance of your own solutions.
That said, partitioning shouldn’t be implemented casually. To set it up and use it correctly, you need a solid understanding of how it works.
The Problem
Imagine a table that grows by a million rows every month, and you need to query or remove two years of history: 100 million rows. You can’t delete that in one statement. There’s rarely enough memory or log space, so you end up deleting it in chunks.
And even once the rows are gone, the disk space isn’t released to the file system until you run DBCC SHRINKFILE, which is another slow operation that usually must be done in pieces as well.
The solution: Partitioning
Partitioning solves both problems. Besides returning results faster on large tables, it lets you remove whole slices of data almost instantly and reclaim the disk space in minutes or even seconds. Here’s how it works end to end.
1) How to Set Up Partitions
For illustration purposes, let’s partition a fact table by year using the date column:
Partition P_20XX holds everything before 2023, and P_2027 holds everything from 2027 onward, that is if there is any data for 2028 or any other year after 2027 it will be stored in P_2027 Partition. Each year in between gets its own partition. You will need to follow these four steps to create partitions.
Step 1: Add a filegroup and a file
Add Files and FileGroup for each Partition. Repeat following for [P_2023], [P_2024]……..[P_2027]
ALTER DATABASE MyDatabase ADD FILEGROUP [P_20XX];
ALTER DATABASE MyDatabase ADD FILE
(
NAME = N’P_20XX’,
FILENAME = N’D:\SQLData\MyDatabase\MyTable\P_20XX.ndf’,
SIZE = 100MB,
FILEGROWTH = 100MB
) TO FILEGROUP [P_20XX];
Change the path and sizes to suit your server. Azure SQL Database doesn’t let you add files or filegroups, so skip this step and use [PRIMARY] in the partition scheme (step 3).
Tip: To manage in a better way, create a separate folder for each database. And inside database folder create separate folder for each table requiring partition and then add files.
Step 2: A partition function that defines the boundaries.
There are 2 ways to define Partition boundaries Left or Right. You can use any one of those based on the requirements or demand of your solution.
1) Right: In this you will define the starting point of the partitions. First partition will have no lower boundary.
Tip: Use values in columns Starts At from above table.
CREATE PARTITION FUNCTION [myPartitionFunction](date)
AS RANGE RIGHT FOR VALUES (‘2023–01–01’, ‘2024–01–01’, ‘2025–01–01’,
‘2026–01–01’, ‘2027–01–01’);
2) Left: If you are using left then you will need to define the end point of the partitions. Last partition will have no upper boundary.
Tip: Use values in columns Ends At from above table.
CREATE PARTITION FUNCTION [myPartitionFunction](date)
AS RANGE LEFT FOR VALUES (‘2022–12–31’, ‘2023–12–31’, ‘2024–12–31’,
‘2025–12–31’, ‘2026–12–31’);
Recommendation: Use Right as your data always grows on the right side.
Step 3: A partition scheme that maps each partition to a filegroup.
We defined five boundaries in step 2, which means that there will be six partitions, so we need six filegroups.
CREATE PARTITION SCHEME [myPartitionScheme]
AS PARTITION [myPartitionFunction] TO
([P_20XX], [P_2023],
[P_2024], [P_2025],
[P_2026], [P_2027]);
Use following in-case of Azure SQL Server
CREATE PARTITION SCHEME [myPartitionScheme]
AS PARTITION [myPartitionFunction] ALL TO ([PRIMARY]);
Step 4: A table created on the scheme, with Date as the partitioning column.
CREATE TABLE [dbo].[myFactTable](
[Column1] [int] NULL,
[Column2] [nvarchar](50) NULL,
/*…other columns…*/
[mDate] [Date] NULL
) ON [myPartitionScheme]([mDate]);
Note: All the Indexes on the table should also follow the same partitioning scheme
B) Adding more partitions in three steps
The partition function doesn’t grow by itself. With RANGE RIGHT, everything from 2027 onward lands in the last partition, P_2027. To give 2028 its own partition, you add a filegroup and a file, tell the partition scheme to use it next, and then split the range. Do this before any 2028 rows arrive: the new partition starts out empty, so no data has to move and the split finishes almost instantly.
Step1: Add a filegroup and a file
ALTER DATABASE MyDatabase ADD FILEGROUP [P_2028];
ALTER DATABASE MyDatabase ADD FILE
(
NAME = N’P_2028',
FILENAME = N’D:\SQLData\MyDatabase\MyTable\P_2028.ndf’,
SIZE = 100MB,
FILEGROWTH = 100MB
) TO FILEGROUP [P_2028];
Change the path and sizes to suit your server. Azure SQL Database doesn’t let you add files or filegroups, so skip this step and use [PRIMARY] in next step.
Step 2: Mark the filegroup as NEXT USED
The NEXT USED filegroup is where SQL Server puts the next partition you create.
ALTER PARTITION SCHEME [myPartitionScheme] NEXT USED [P_2028];
Use following in-case of Azure SQL Server
ALTER PARTITION SCHEME [myPartitionScheme] NEXT USED [PRIMARY];
Step 3: Split the range
ALTER PARTITION FUNCTION myPartitionFunction() SPLIT RANGE (‘2028–01–01’);
With RANGE RIGHT, the new partition is the one to the right of the new boundary. P_2028 now holds everything from 2028 onward, and P_2027 keeps 2027 like below.
Each split uses up the NEXT USED setting, so repeat all three steps every year. A scheduled SQL Agent job works well for this.
Note: If your function is RANGE LEFT, the new boundary is 2027–12–31. The new partition is then the one to the left of that boundary, and it covers 2027, which already has data. SQL Server has to move all of those rows, which is slow and fully logged. That’s one more reason to use RANGE RIGHT for date-based partitions.
C) Deleting two years of data in six steps
Say we want to get rid of 2023 and 2024. Instead of deleting rows, we’ll move those partitions out of the table, empty them, and then collapse the partition structure.
Step 1: Create a staging table with the same structure
Create an empty table with the same columns, the same partition scheme and, importantly, the same indexes as the original. The switch in Step 2 fails if they don’t match.
CREATE TABLE [dbo].[myFactTableTemp](
[Column1] [int] NULL,
[Column2] [nvarchar](50) NULL,
/*…same columns as myFactTable…*/
[mDate] [date] NULL
) ON [myPartitionScheme]([mDate]);
/*Recreate the same indexes that exist on [dbo].[myFactTable]*/
Step 2: Switch the partitions out
Because the function is RANGE RIGHT, partition 1 is XX (before 2023), partition 2 is 2023 and partition 3 is 2024. Switch those two into the staging table:
ALTER TABLE [dbo].[myFactTable]
SWITCH PARTITION 2 TO [dbo].[myFactTableTemp] PARTITION 2;
ALTER TABLE [dbo].[myFactTable]
SWITCH PARTITION 3 TO [dbo].[myFactTableTemp] PARTITION 3;
A switch only changes metadata, so it finishes in seconds no matter how many rows the partition holds. The rows now live in the staging table and are gone from the original.
Step 3: Truncate the staging table
Delete the data with TRUNCATE rather than DELETE. It deallocates whole pages instead of removing rows one by one, so it’s near-instant and doesn’t need the memory and log space a large DELETE would.
TRUNCATE TABLE [dbo].[myFactTableTemp];
Get Fahad Shamim’s stories in your inbox
Join Medium for free to get updates from this writer.
Step 4: Merge the empty partitions
2023 and 2024 are now empty, so remove their boundaries. Their ranges fold into the XX partition. Merging (and splitting) is only quick when the partitions involved are empty; on populated partitions SQL Server has to physically move the data.
ALTER PARTITION FUNCTION myPartitionFunction() MERGE RANGE (‘2023–01–01’);
ALTER PARTITION FUNCTION myPartitionFunction() MERGE RANGE (‘2024–01–01’);
After the merge, the partitions look like this:
Step 5: Remove the empty files
With 2023 and 2024 merged away, their files no longer hold any data. You can remove them, provided no other table or index still uses them.
ALTER DATABASE MyDatabase REMOVE FILE P_2023;
ALTER DATABASE MyDatabase REMOVE FILE P_2024;
Tip: REMOVE FILE takes the logical file name, not the filegroup name. Once a filegroup has no files left, you can drop it as well with ALTER DATABASE MyDatabase REMOVE FILEGROUP <filegroup_name>.
Step 6: Drop the staging table (Optional)
The staging table has done its job, so drop it or you can keep it as you will need it next year.
DROP TABLE [dbo].[myFactTableTemp];
That’s it. Two years of data are gone and the disk space is back, without a single long-running DELETE or SHRINKFILE.
Things to watch out for
· The staging table must match the source exactly: same columns, data types, nullability and indexes. It must also sit on the same filegroup as the partition you’re switching; creating it on the same partition scheme takes care of that.
· The target partition must be empty before you switch into it.
· Merge and split partitions only when they’re empty. On populated partitions SQL Server has to physically move rows, which is slow and fully logged.
· Know whether your function is RANGE LEFT or RANGE RIGHT. It decides which partition each boundary value lands in, and therefore which partition numbers you switch and which boundaries you merge.
D) Partitioning in Microsoft Fabric
Fabric has several places to store tables, and each one handles partitioning differently. Before you copy the scripts above into Fabric, check which one you’re using.
SQL database in Fabric
SQL database in Fabric runs the same engine as Azure SQL Database, so the partition function, scheme, SWITCH, SPLIT and MERGE all work as shown above. The one difference is storage: you can’t add files or filegroups, so every partition goes on PRIMARY. Skip the “Add a filegroup and a file” and “Remove the empty files” steps, and map the whole scheme to PRIMARY:
CREATE PARTITION SCHEME [myPartitionScheme]
AS PARTITION [myPartitionFunction] ALL TO ([PRIMARY]);
/*Before each SPLIT similar to Azure Sql Server*/
ALTER PARTITION SCHEME [myPartitionScheme] NEXT USED [PRIMARY];
Fabric Data Warehouse
Fabric Data Warehouse doesn’t support partitioned tables. CREATE PARTITION FUNCTION, partition schemes and SWITCH aren’t available there. Tables are stored as Delta/Parquet files in OneLake, and Fabric manages the physical layout for you.
If your queries filter heavily on a column such as a date, look at data clustering instead (in preview at the time of writing). It stores rows with similar values together so the engine can skip files that don’t match your filter. You define it when you create the table, and you can’t add it to an existing table:
CREATE TABLE [dbo].[myFactTable]
(
[Column1] INT,
[Column2] VARCHAR(50),
[mDate] DATE
)
WITH (CLUSTER BY ([mDate]));
Fabric Lakehouse (Delta tables)
In a Lakehouse, partitioning is done by Spark. Each distinct value of the partition column gets its own folder of Parquet files. Partition on a low-cardinality column such as the year, not on the raw date, or you’ll end up with thousands of tiny files.
# PySpark: write a Delta table partitioned by year
from pyspark.sql.functions import year
(df.withColumn(“Year”, year(“mDate”))
.write.format(“delta”)
.mode(“overwrite”)
.partitionBy(“Year”)
.saveAsTable(“myFactTable”))
Or
%%sql
CREATE TABLE myFactTable (
Column1 INT,
Column2 STRING,
/*…other columns…*/
mDate DATE,
Year INT
)
USING DELTA
PARTITIONED BY (Year);
Removing old years is the Lakehouse version of the switch-and-truncate steps. A DELETE whose filter uses only the partition column drops whole partitions without rewriting any files. Delta keeps the old files for time travel, so run VACUUM to reclaim the storage. The default retention period is seven days.
--Spark SQL
DELETE FROM myFactTable WHERE Year IN (2023, 2024);
VACUUM myFactTable RETAIN 168 HOURS;
Note: Microsoft’s current guidance says that with Fabric Runtime 2.0, clustering is recommended for most workloads where the goal is read/query performance. Partitioning is primarily recommended when you need to isolate concurrent writes to different parts of the table. Partitioning and clustering can’t be used on the same table.
Managing partitions for Fabric LakeHouse is much simpler compared to SQL Server or Azure SQL Server.
Which one should you use?
· You need classic partition switching for fast archiving: use SQL database in Fabric (or SQL Server / Azure SQL).
· You’re building an analytical warehouse in T-SQL: use Fabric Data Warehouse with data clustering on your most common filter column.
· You load and transform data with Spark: use a Lakehouse with clustering for read performance. Partition by a low-cardinality column such as the year only when you need to isolate concurrent writes.
Wrapping up
Partitioning turns “deletion / archiving” from an all-night job into a handful of quick metadata operations. Set partitioning up before your tables grow large, and both growth and archiving become routine tasks rather than projects. As a bonus, everyday queries that filter on the partition key can run noticeably faster, because the engine only needs to read the relevant partitions.
A few things to take away from this post:
· Plan the layout up front. Use RANGE RIGHT for date-based partitions, give each year its own filegroup (or PRIMARY in Azure SQL Database and SQL database in Fabric), and make sure every index on the table uses the same partition scheme. Changing the design later is far harder than getting it right on day one.
· Add partitions before the data arrives. The partition function never grows by itself. Each year, add the filegroup and file, mark it NEXT USED, and split the range before the first row for that year lands. An empty partition splits instantly; a populated one forces SQL Server to move rows, which is slow and fully logged.
· Automate the sliding window. Adding next year’s partition and archiving the oldest one are two halves of the same yearly routine. Put both in a scheduled SQL Agent job so the table always has a spare empty partition on the right and never keeps more history than it needs.
· Archive by moving metadata, not rows. Switch, truncate, merge and remove files. Every step is quick because it only touches metadata or empty partitions, so two years of data disappear without a long-running DELETE or SHRINKFILE.
· Pick the right option in Fabric. SQL database in Fabric supports everything in this post, with all partitions on PRIMARY. Fabric Data Warehouse doesn’t support partitioned tables, so use data clustering on your most common filter column instead. In a Lakehouse, use clustering for read performance and keep Delta partitioning, on a low-cardinality column such as the year, for isolating concurrent writes. Remove old years there with a DELETE on the partition column followed by VACUUM.
· Check before you act. Run the queries below to confirm partition numbers, boundaries, row counts and file usage before any switch, merge, split or file removal. Most partitioning mistakes come from acting on the wrong partition number.
Useful queries for working with partitions SQL Server / Azure SQL Server
1. Find the tables that are partitioned
SELECT DISTINCT t.name
FROM sys.partitions p
INNER JOIN sys.tables t
ON p.object_id = t.object_id
WHERE p.partition_number <> 1;
2. Row count per partition, with its range and filegroup
SELECT DISTINCT
o.name AS table_name,
ps.name AS PScheme,
f.name AS PFunction,
rv.value AS partition_range,
fg.name AS file_groupName,
p.partition_number,
p.rows AS number_of_rows
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE o.object_id = OBJECT_ID(‘[dbo].[myFactTable]’)
ORDER BY p.partition_number;
3. The same details, plus logical and physical file names
SELECT DISTINCT
o.name AS table_name,
ps.name AS PScheme,
f.name AS PFunction,
rv.value AS partition_range,
fg.name AS file_groupName,
df.name AS logical_file_name,
df.physical_name AS physical_file_name,
p.partition_number,
p.rows AS number_of_rows
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.database_files df ON df.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE o.object_id = OBJECT_ID(‘[dbo].[myFactTable]’)
ORDER BY p.partition_number;
4. Partition details with range, function and scheme
SELECT
OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
,OBJECT_NAME(pstats.object_id) AS TableName
,ps.name AS PartitionSchemeName
,ds.name AS PartitionFilegroupName
,pf.name AS PartitionFunctionName
,CASE pf.boundary_value_on_right WHEN 0 THEN ‘Range Left’ ELSE ‘Range Right’ END AS PartitionFunctionRange
,CASE pf.boundary_value_on_right WHEN 0 THEN ‘Upper Boundary’ ELSE ‘Lower Boundary’ END AS PartitionBoundary
,prv.value AS PartitionBoundaryValue
,c.name AS PartitionKey
,CASE
WHEN pf.boundary_value_on_right = 0
THEN c.name + ‘ > ‘ + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.partition_number), ‘MIN’) AS VARCHAR(100))
+ ‘ and ‘ + c.name + ‘ <= ‘ + CAST(ISNULL(prv.value, ‘MAX’) AS VARCHAR(100))
ELSE c.name + ‘ >= ‘ + CAST(ISNULL(prv.value, ‘MIN’) AS VARCHAR(100))
+ ‘ and ‘ + c.name + ‘ < ‘ + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.partition_number), ‘MAX’) AS VARCHAR(100))
END AS PartitionRange
,pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
,p.data_compression_desc AS DataCompression
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id
AND pstats.index_id = i.index_id
AND dds.partition_scheme_id = i.data_space_id
AND i.type IN (0, 1, 5)
INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv
ON pf.function_id = prv.function_id
AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE prv.boundary_id + 1 END)
WHERE pstats.object_id = OBJECT_ID(‘[dbo].[myFactTable]’)
ORDER BY TableName, PartitionNumber;
5. Size and free space of each database file
WITH Cte_Q AS
(
SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0
— CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)/128.0
AS FreeSpaceMB,
size/128.0/1024 AS CurrentSizeGB,
(size/128.0
— CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)/128.0)/1024
AS FreeSpaceGB
FROM sys.database_files
WHERE type IN (0,1)
)
SELECT *,
CONVERT(float, ROUND(FreeSpaceMB/CurrentSizeMB*100, 2)) AS [FreeSpace%]
FROM Cte_Q;
6. Find which partition a row belongs to
$PARTITION.<function_name>(value) returns the partition number for any value. It’s handy for confirming partition numbers before you switch anything.
SELECT TOP 100 *,
$PARTITION.myPartitionFunction(mDate) AS PartitionNumber
FROM [dbo].[myFactTable];
Follow for more practical content on Microsoft Fabric, Power BI, modern data analytics and data engineering.
