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.
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%')
drop table #mytable1
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)
declare @i int
insert into #myTable1
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)
dbcc page('tempdb', 1, 376,3)
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.