Reading that title, you might sit and wonder why you would ever want to partition a temporary table. I too would wonder the same thing. That withstanding, it is an interesting question that I wanted to investigate.
The investigation started with a fairly innocuous venture into showing some features that do apply to temp tables which are commonly mistaken as limitations (i.e. don’t work with temp tables). To show this I set off to create a script with reproducible results to demonstrate these features. I have included all of those in the same script I will provide that demonstrates the answer to the partitioning question.
In fact lets just jump to that script now.
[codesyntax lang="tsql"]
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('#hubbabubba','U') IS NOT NULL
BEGIN
DROP TABLE #hubbabubba;
END
CREATE TABLE #hubbabubba (
someint INT PRIMARY KEY NONCLUSTERED IDENTITY(1,1)
,somechar VARCHAR(50)
,somedate DATE
,somebit BIT DEFAULT(0))
IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name = 'PartitionToPrimary')
BEGIN
DROP PARTITION SCHEME PartitionToPrimary
END
IF EXISTS (SELECT name FROM sys.partition_functions WHERE name = 'PartitionByMonth')
BEGIN
DROP PARTITION FUNCTION PartitionByMonth
END
CREATE PARTITION FUNCTION PartitionByMonth (DATE)
AS RANGE RIGHT
FOR VALUES ('2014/01/01', '2014/02/01', '2014/03/01', '2014/04/01', '2014/05/01','2014/06/01'
, '2014/07/01', '2014/08/01', '2014/09/01', '2014/10/01', '2014/11/01', '2014/12/01');
CREATE PARTITION SCHEME PartitionToPrimary
AS PARTITION PartitionByMonth
ALL TO ([PRIMARY]);
CREATE CLUSTERED INDEX idx_hubba_somedate ON #hubbabubba (somedate)
ON PartitionToPrimary (somedate);
GO
/*
Establish a Date range to be used for Random date generation and table population
We only have the table partitioned for the current year so limiting the dates to this
year is essential
*/DECLARE @BeginDate DATE = '2014-01-01'
,@EndDate DATE = '2014-12-31'
/* Populate some data */INSERT INTO #hubbabubba
( somechar,somedate )
VALUES ( 'DidmyDefaultApply?'
,DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate))
GO 5000
SELECT *
FROM #hubbabubba;
USE tempdb;
GO
sp_help '#hubbabubba'
/* Demonstrates the existence of
2 constraints on the temp table
2 indexes on the temp table
1 clustered (supports the partition)
1 nonclustered
*//* Base query for the following attributed to Kendra Little
This demonstrates that Partitions can be created on temp tables
*/SELECT OBJECT_NAME(si.object_id) AS object_name ,
pf.name AS pf_name ,
ps.name AS partition_scheme_name ,
p.partition_number ,
rv.value AS range_value ,
SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
ELSE 0
END) AS num_rows ,
SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,
SUM(CASE ISNULL(si.index_id, 0)
WHEN 0 THEN 0
ELSE 1
END) AS num_indexes
FROM sys.destination_data_spaces AS dds
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
LEFT OUTER JOIN sys.partition_range_values AS rv
ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT OUTER JOIN sys.indexes AS si
ON dds.partition_scheme_id = si.data_space_id
LEFT OUTER JOIN sys.partitions AS p
ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.dm_db_partition_stats AS dbps
ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
WHERE p.OBJECT_ID = OBJECT_ID('#hubbabubba','U')
GROUP BY p.partition_number ,pf.name,ps.name ,si.object_id ,rv.value;
GO[/codesyntax]
In the beginning (after dropping objects if they exist), I start by creating a temp table that has a couple of mythical limitations. These mythical creatures are that temp tables can’t have indexes or that they can’t have constraints.
In this script, I show that a temp table (#hubbabubba) can indeed have indexes created on it (clustered and nonclustered). I also demonstrate the creation of two different kinds of constraints on the #hubbabubba table. The two constraints are a primary key and a default constraint. That stuff was easy!!
To figure out whether or not one could partition a temporary table, I needed to do more than simply create a “test” temp table. I had to create a partitioning function and a partitioning scheme and then tie that partition scheme to a clustered index that I created after table creation. Really, this is all the same steps as if creating partitioning on a standard (non-temporary) table.
With that partitioning scheme, function and the table created it was time to populate with enough random data to seem like a fair distribution. You see, I created a partition function for each month of the year 2014. To see partitioning in action, I wanted to see data in each of the partitions.
That brings us to the final piece of the whole script. Kendra Little produced a script for viewing distribution of data across the partitions so I used her script to demonstrate our data distribution. If you run the entire script including the data distribution segment at the end, you will see that there are 13 partitions with each of the monthly partitions containing data.
The distribution of data into the different partitions demonstrates soundly that partitioning can not only be created on a temporary table, but that it can be used. As for the secondary question today “Why would you do that?”, I still do not know. The only reason that pops into my mind is that you would do it purely for demonstration purposes. I can’t think of a production scenario where partitioning temporary data would be a benefit. If you know of a use case, please let me know.
