Azure SQL Data Compression | Row | Page | Prefix Dictionary |
Welcome to Praasy Technologies
This video session explains you about the
1.) What is Azure SQL data compression
2.) How to reduce the size of the table by applying compression feature
Links that were discussed inside the video session
How to create new Azure SQL Server
• Azure SQL Server Full Demo with On-Premise...
--Master Query to analyze the details of the table
SELECT i.object_id AS ID ,OBJECT_NAME(i.object_id) AS TableName
,p.rows as Total_Records ,(8 * SUM(a.used_pages))/1024 AS 'Size_MB'
,8 * SUM(a.used_pages) AS 'Size_KB' ,p.data_compression as Data_Comp_Ind ,p.data_compression_desc as Data_Comp_Desc
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
where OBJECT_NAME(i.object_id) = 'table_million'
GROUP BY i.OBJECT_ID, i.index_id, i.name,p.rows,p.data_compression,p.data_compression_desc
--Query to apply compression on existing table
ALTER TABLE Table_Million
REBUILD with (DATA_COMPRESSION = Page);
GO
--Query to apply compression on new table
CREATE TABLE Table_Test (
FirstColumn int,
SecondColumn varchar(50))
WITH (DATA_COMPRESSION = Page);
GO
--Query table compression details
select t.name, p.* from sys.partitions p
join sys.tables t on p.object_id = t.object_id
where t.name = 'Table_Test'
Thank you
Support me by subscribing to my channel for more videos
Have a great day
-
Prakash Rajamani
Azure Senior Data Engineer
Bangalore
India
#AzureSqlServer
#Datacompression
#SQLDatacompression
#SQLcompression
#PrefixAlgorithm
#DictionaryAlgorithm
#Azurecompression
#praasy
#PrakashRajamani
#praasytechnolgies