Hi,

I am looking into compression ratio calculations and varchar storage. I cannot understand the logic of the varchar data_size. the following query should give results where the the data_size is 0 for varchars, but that is not consistent for all the column and tables. Some varchar columns (greater than 8 byte) will have a non zero data_size. However the compressed_Data_size is definitely not zero.

select cc.TABLE_SCHEMA,cc.TABLE_NAME,cc.COLUMN_NAME,cc.COLUMN_LENGTH,sum(data_size) ct,cc.OBJECT_ID,cc.DICTIONARY_OBJECT_ID,cc.DATA_TYPE,cc.COMPRESSION_TYPE
from information_schema.columnstore_extents ce
left join information_schema.columnstore_columns cc on ce.object_id = cc.DICTIONARY_OBJECT_ID – (or cc.object_id)
where compression_type='Snappy' and DATA_SIZE =0 group by cc.TABLE_SCHEMA,cc.TABLE_NAME,cc.COLUMN_NAME having ct=0;

Any light on this ?