Partitioning a Huge Table Quickly

August 4, 2026

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication — Michael J. Swart @ 2:59 pm

This is an update to my post last week Partitioning a Huge Table where I talk about taking an existing table and making it partitioned.

My largest complaint in that post was that it was difficult to do online because rebuilding a clustered index on a huge table required reading or writing a lot of data.
Also if I wanted to take advantage of partition switching, it was still tricky because it required adding a check constraint which also took a long amount of time.

Was there any way to adopt partitioned tables for huge tables without incurring a size-of-data operation? I invited people to leave their ideas in the comments. A reader who calls himself mmiike delivered.

Here’s a link to his comment where he says:

you can avoid the CHECK constraint nonsense entirely if you create the initial partition function empty – i.e. with no values
…
since a partition function with no boundaries assigns everything to partition 1, there is no check constraint required in source or target tables.

He goes on to describe a way to get your “foot in the door” by switching the data into a partitioned table that uses a partition function with no boundaries, i.e. one partition. Then make use of ALTER PARTITION FUNCTION ... SPLIT RANGE... to add the extra partitions.

Visualized

I think of the process this way:

Code example

I checked out his idea and he’s absolutely right:

Setup the example:

DROP TABLE IF EXISTS dbo.HumongousTable
DROP TABLE IF EXISTS dbo.HumongousTable_Temp
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_MonthlySlidingWindow')
BEGIN
	EXEC sp_executesql N'DROP PARTITION SCHEME PS_MonthlySlidingWindow';
	EXEC sp_executesql N'DROP PARTITION FUNCTION PF_MonthlySlidingWindow';
END
GO
 
CREATE TABLE dbo.HumongousTable /* Non-partitioned */
(
	Id INT NOT NULL IDENTITY,
	Name NVARCHAR(100) NOT NULL,
	Description NVARCHAR(500) NULL,
	LogDate DATETIME2 NOT NULL,
	CONSTRAINT PK_HumungousTable 
		UNIQUE CLUSTERED (LogDate, Id),
);
INSERT dbo.HumongousTable(Name, Description, LogDate)
SELECT CAST(text AS NVARCHAR(100)), CAST(text AS NVARCHAR(500)), GETUTCDATE()
FROM sys.messages 
UPDATE dbo.HumongousTable SET LogDate = dateadd(second, id * -1, logdate);
GO

The fast part
This part takes zero seconds and does no size-of-data scans:

/* Create the partition function */
CREATE PARTITION FUNCTION PF_MonthlySlidingWindow (DATETIME2)
AS RANGE RIGHT
FOR VALUES
(
	/* no partition boundaries to start with */
);
 
/* Create the partition scheme */
CREATE PARTITION SCHEME PS_MonthlySlidingWindow
AS PARTITION PF_MonthlySlidingWindow
ALL TO ([PRIMARY]);
 
/* create an empty non-partitioned table matching HumongousTable_Temp exactly */
CREATE TABLE dbo.HumongousTable_Temp /* Non-partitioned */
(
	Id INT NOT NULL IDENTITY,
	Name NVARCHAR(100) NOT NULL,
	Description NVARCHAR(500) NULL,
	LogDate DATETIME2 NOT NULL,
	CONSTRAINT PK_HumungousTable_Temp 
		UNIQUE CLUSTERED (LogDate, Id),
);
 
/* stash the current data, switch is metadata only */
ALTER TABLE dbo.HumongousTable
SWITCH TO dbo.HumongousTable_Temp;
 
/* rebuild (the now empty) PK_HumungousTable on the partition scheme */
CREATE UNIQUE CLUSTERED INDEX PK_HumungousTable 
	ON dbo.HumongousTable(LogDate, Id)
	WITH ( DROP_EXISTING = ON ) 
ON PS_MonthlySlidingWindow(LogDate);
 
/* switch data back, still fast */
ALTER TABLE HumongousTable_Temp 
SWITCH TO HumongousTable PARTITION 1 
 
IF NOT EXISTS (SELECT * FROM dbo.HumongousTable_Temp)	
BEGIN
	DROP TABLE IF EXISTS dbo.HumongousTable_Temp 
END
 
DECLARE @Month DATETIME2 = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
WHILE @Month < '20300101' /* or whenever you plan to retire ;-) */
BEGIN
	SET @Month = DATEADD(MONTH, 1, @Month);
	ALTER PARTITION FUNCTION PF_MonthlySlidingWindow() SPLIT RANGE (@Month)
	ALTER PARTITION SCHEME PS_MonthlySlidingWindow NEXT USED [PRIMARY] 
END
 
/* About 42 new partitions: */
SELECT COUNT(*) as NumberOfPartitions
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.HumongousTable')

A great comment

I love this kind of update. I got a comment on my blog that helped me learn something new that I couldn’t get out of a Google search or DBA.StackExchange or from AI.
At the moment, my blog remains relevant. At least a little bit. At the very least, it helped me learn how to spell humongous properly. 😀

As a corollary, we can reverse this and use it to create a trusted check constraint quickly.

-- Comments (1)

1 Comment »

[…] Update! Aug. 4, 2026 Don’t miss the comments below, or my follow up post Partitioning a Huge Table Quickly […]

Pingback by Partitioning a Huge Table | Michael J. Swart Michael J. Swart — August 4, 2026 @ 3:03 pm

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

August 4, 2026