Sql Server : ALTER INDEX REBUILD does not change data COMPRESSION status of the table
- Posted by Sqltimes
- On July 4, 2015
- 0 Comments
Quick one today:
Earlier, there was an interesting question that came up during a discussion. When we perform ‘ALTER INDEX ALL ON dbo.test REBUILD’ does it change the data COMPRESSION status of the table?
In short, no. It keeps it the way it was before the REBUILD started.
Since this is a REBUILD (without changing the definition), to reduce the fragmentation, my guess is it does not read the contents of the data (in detail). It will read it just enough to write to a different contiguous location. So, there is no un-compressing and re-compressing operation going on here.
But if we mention the index option “DATA_COMPRESSION = PAGE”, it will compress the data, no matter what the previous state.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
---- Sample code to test--CREATE TABLE dbo.test ( , ID INT IDENTITY(1,1) PRIMARY KEY , Name1 VARCHAR(50) DEFAULT CONVERT(VARCHAR, (RAND()*10000)) , Name2 VARCHAR(50) DEFAULT CONVERT(VARCHAR, (RAND()*10000)) , Name3 VARCHAR(50) DEFAULT CONVERT(VARCHAR, (RAND()*10000)) )GO---- Put some dummy data--INSERT INTO dbo.test DEFAULT VALUESGO 1000 |
Now, lets check the compression status of the CLUSTERED index.
|
1
2
3
4
5
6
7
|
---- Check the compression status--SELECT OBJECT_NAME(object_id), data_compression_descFROM sys.partitionsWHERE object_id = OBJECT_ID('test')AND index_id = 1 |
As you can see, the table data is not compressed.
Table Not Compressed
Now, when we REBUILD, the compression status does not change.
|
1
2
3
4
5
|
---- Now REBUILD without any INDEX OPTIONS.--ALTER INDEX ALL ON dbo.test REBUILDGO |
As part of REBUILD, once we add any changes to the definition of the index, the compression status changes.
|
1
2
3
4
5
6
|
---- Now REBUILD and test.--ALTER INDEX ALL ON dbo.test REBUILDWITH (DATA_COMPRESSION = PAGE)GO |
Rebuild with COMPRESSION change
Hope this helps,
_Sqltimes
0 Comments