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.
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
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.