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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
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.
Jason–
Cool post–One thing I wanted to add is that you can also compress a temp table. We had a use case in a complex procedure where I thought it might have been a faster solution, but on that hardware it wasn’t. Main thing is that we were extremely IO constrained on tempdb. Write latencies of 300ms.
Thanks Joey. I should do a follow up on temp table compression. I have it in my presos on compression. I think it would make a nice follow up post 😉
Nice post Jason.
I sure hope that a temp table never gets so big that partitioning becomes desirable!
Thanks Wayne.
I was asked the same question once in my interview , i said yes but at that i dont know how but i learned that now. Thanks a lot for knowledge share.
Never thought of that. As Wayne mentioned, I hope there won’t be any need to partition temp tables. But thanks for the wonderful posts. Its funky though!!!
I came up with one possible usage, which is certainly not a best practice. So do not do this at home.
Given you have an external script in Python or in R which you want to parallelize, and Revoscale is not an option.
Then you may:
1. Load your data in a temporary table
2. Apply horizontal partitioning
3. Apply asynchronous procedure execution over the partitions
I want to test this out this week or next week. I will share the results.
Hi
Could you please let me know sample script for horizontal partition and also please share if your testing was successful. even I have same issue and want to tune plsql which uses global temp table.
Thanks in advance
Would not partitioning temporary tables be very useful if you combine it partitioning switching? For example, if you are creating a result set in a temporary table because of latch contention. As in, wrapping begin trans/end trans around SP code that deals with large volume/complex SQL processing can lock out a table for minutes, preventing multiple users from inserting into the table concurrently. If instead you create your result set in a temporary partitioned table, you can switch the result into the final target table as long as the partitioning infrastructure of the tables matches.
That would be an excellent idea. I have not tried that approach. It seems like it should work. I will have to test it out. If you have a test that proves it works already, I would love for you to share it here.