Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Can you partition a temporary table?

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.

Comments

Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...