Data Compression of MS SQL Server
Data Compression of MS SQL Server
Join the DZone community and get the full member experience.
Join For FreeSensu is an open source monitoring event pipeline. Try it today.
Step – 1 [ Create The Base Table ] IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_EMPLOYEE]; END GO CREATE TABLE [dbo].[tbl_EMPLOYEE] ( EMPID INT, EMPNAME VARCHAR(50) ) GO Step – 2 [ Insert Some Records ] INSERT INTO [dbo].[tbl_EMPLOYEE] (EMPID, EMPNAME) VALUES(1, 'Joydeep Das'); GO 5000 Step - 3 [ Check the Space used by Original Table ] EXEC sp_spaceused tbl_EMPLOYEE
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
tbl_EMPLOYEE
|
5000
|
200KB
|
152KB
|
8KB
|
40KB
|
Step – 4 [ Now we Used Data Compression ROW ] ALTER TABLE [dbo].[tbl_EMPLOYEE] REBUILD WITH (DATA_COMPRESSION = ROW); GO Step – 5 [ Now Again Check the Space Used After Data Compression ROW ] EXEC sp_spaceused tbl_EMPLOYEE
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
tbl_EMPLOYEE
|
5000
|
144KB
|
120KB
|
8KB
|
16KB
|
Step – 6 [ Now we Used Data Compression PAGE ] ALTER TABLE [dbo].[tbl_EMPLOYEE] REBUILD WITH (DATA_COMPRESSION = PAGE); GO Step – 7 [ Now Again Check the Space Used After Data Compression PAGE ] EXEC sp_spaceused tbl_EMPLOYEE
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
tbl_EMPLOYEE
|
5000
|
80KB
|
64KB
|
8KB
|
8KB
|
Step – 8 [ Now We Comare all Again ]
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
tbl_EMPLOYEE
|
5000
|
200KB
|
152KB
|
8KB
|
40KB
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
tbl_EMPLOYEE
|
5000
|
144KB
|
120KB
|
8KB
|
16KB
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
tbl_EMPLOYEE
|
5000
|
80KB
|
64KB
|
8KB
|
8KB
|
Compression Type
|
Data
|
NO Compression (Original)
|
152KB
|
Compression ROW
|
120KB
|
Compression PAGE
|
64KB
|
Sensu: workflow automation for monitoring. Learn more—download the whitepaper.
Published at DZone with permission of Joydeep Das , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}