Bradley Ball is a MCITP SQL 2005 & MCTS 2008 DBA with over 10 years of IT experience. Bradley spent 8 years working as a Defense contractor for clients such as the U.S. Army and The Executive Office of the President of the United States. He is currently a Sr. Consultant for Pragmatic Works. He has presented at SQL Saturdays 62, 74, 79, 85, 86, 131, for the MAGICPASS & OPASS SSUG’s, SQL Rally 2011 & 2012, SQL Dev Connections 2012, the PASS Summit in 2011, and is scheduled for the PASS Summit 2012 and SQL Live 360 later this year. He recently finished Chapter 14 of Expert SQL Server Practices on Page & Row Compression and can be found blogging on http://www.sqlballs.com.
@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.
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.