This article about ,
How to compress XML data in SQL 2022? or How SQL Server 2022 can help optimize storage with XML data compression.
SQL Server 2022 new features : XML Compression
SQL Server 2022 come up with a lot of new features and update which can help to improve SQL Server performance and management.
XML compression is one of the key feature of SQL Server 2022. If you have large SQL Server database and table storing XML data , this feature is going to help a lot of disk space and save a cost.
How to implement XML compression on existing table?
Pre- requisite : How to compress XML data in SQL 2022?
- SQL Server 2022
- Data type should be XML.
- Sufficient(2 time size of table if rebuild of existing table) drive space for log file and tempDB.
There are 2 approach can be used for How to compress XML data in SQL 2022?
- Rebuild existing table with XML compression.
- Create new table with same structure with XML compression on and then move data.
How to compress XML data in SQL 2022? : Approach 1
Rebuild existing table with XML compression.
In this approach, existing table will be rebuild using alter table rebuild with XML compression on.
This process will generate almost double the size of log.
If XML data type is different then first need to change it to XML and then apply compression.
Time required to complete this rebuild is depend on table size and log file space and speed. A lot of write operation is involved in this task.
Table will be unavailable during this activity so downtime window required to perform this activity.
How to compress XML data in SQL 2022? : Approach 2
Create new table with same structure with XML compression on and then move data.
This is another way to compress XML data.
Create new table with same structure and add XML compression on table and indexes.
Then move the data to new table from original table. This activity generate less log than approach 1. But still need free disk space same as original table size.
Data movement can be done in batches to avoid issue.
Data movement time depends on write speed of drive. If speed is good it get competed faster.
In this approach no downtime needed and data movement can be done side by side in non working hours.
Once all data moved we can get small downtime window to move incremental data and make the XML compressed table and original and drop the big size table.
How much XML compression can be achieved?
You can get up to 80 percent compression with XML compression on. Like size of 1 TB table reduce to 150 GB with XML compression on.
Also data integrity will be maintained with this compression and also performance will be not impacted much.