Transparent compression of LOB-type columns
In SqlServer 2016 two new data compression scalar functions were added (COMPRESS and DECOMPRESS).
However, it would be good to have column data compression mechanism implemented internally as a part of the storage engine and transparent to query engine.
Syntactically it could look as
CREATE TABLE [Table]
[Column] nvarchar(MAX) compressed NULL
where 'compressed' is the keyword (like 'sparse' for example), which can be applied to LOB-type columns, such as varchar(max), nvarchar(max), varbinary(max), xml, etc.
Functionally it could work as follows.
When column is declared with 'compressed' modifier, the queries like
SET [Column] = 'AAAA'
internally should work as
SET [Column] = COMPRESS('AAAA')
SELECT DECOMPRESS([Column]) AS [Column]
So, that, when data is being selected it decompresses first. And when data is being updated the replacing value compresses first.
Likely all of the varchar(max) compressed, nvarchar(max) compressed, varbinary(max) compressed internally will be the same as varbinary(max).
However, please preserve original type information so that it correctly shown in the sys.columns.
Upvotes: 3<=-=Mar 21 2017 1:08PM=-=>
Thank you for your feedback!
We will consider this feature for the future releases of SQL Server and Azure SQL Database.
When is memorizing thinking? People recite the pledge of allegiance without thinking about what it means.
would also be nice if this could just be activated like the row/page storage compression without having to change the schema.