Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Can You Compress a Temp Table?

  • 24 September 2013
  • Author: BradleyBall
  • Number of views: 9317
  • 0 Comments

 Hello Dear Reader!  We are finishing up the final day of the Performance Tuning Workshop here in Atlanta and I got an interesting question on Compression from Tim Radney (@tradney | Blog).

 
The question: Can you compress a temp table? Just a quick blog to get the answer out there while Gareth Swanepoel (@GarethSwan | Blog)  teaches the class about Extended Events. 
 
My guess was yes.  Temp Tables can have statistics, Clustered and Non-Clustered Indexes, while they only exist in the session they are created, I would be they could be compressed.  If you would actually want to compress them is a different discussion, but let’s prove this out.
 
DEMO
 
Here’s a quick demo to show you can do this.  So first up we will create our Temp Table specifying with Data_Compression=ROW.
 
This will create our temp table #myTable1, we will then insert 15000.
 
if exists(select name from tempdb.sys.tables where name like '#myTable1%')
begin
     drop table #mytable1
end
go
create table #myTable1(
              myid int identity(1,1) primary key clustered
              ,mychar1 char(500) default 'a'
              ,mychar2 char(3000) default 'b'
              ) with (data_compression=row)
go
declare @i int
set @i=0
while(@i<5000)
begin
     set @i=@i+1
     insert into #myTable1
     default values
end
 
Now let’s use DBCC IND to view the pages associated with our table, and DBCC Page to Validate that our data is compressed.
 
dbcc ind(tempdb, '#myTable1', 1)
go
 

 

 

 

dbcc traceon(3604)
go
dbcc page('tempdb', 1, 376,3)
go
 

 

 

 

Looking at the output of DBCC Page I can see that the CD array for my compressed data is present near the header.  Row compression is indeed on.
 
Now let’s rebuild this using page compression on a rebuild operation using sp_spaceused to measure the size of the table.
 

 

And it is now Page Compressed.  Thanks for the question Tim!  And as always Dear Reader Thank you for stopping by.

 

 
Thanks,
 
Brad
 
 

 

 

 

Print
Categories: Performance Tuning
Tags:
Rate this article:
No rating

BradleyBallBradleyBall

Other posts by BradleyBall

Please login or register to post comments.