Sql-server – How does combining columns save disk space

database-designdisk-spaceindexsql serverstorage

I am creating a table in SQL server and inserting records. Three columns from this table are AAA int, AAB int and AAC int. If I merge AAB and AAC into one column Comb1 char(2), it consumes 20% less disk space compared with three original columns. But if I merge all three into one column Comb2 char(3), it still saves 20% disk space.

Can anyone explain this? And does the machine sort a combined column faster than sorting two columns independently?

Many thanks!

Best Answer

int and char(1) are not the same.

int stores a 32 signed whole number, 4 bytes
char(1) stores a single characters, 1 byte (on SQL Server, does not use UTF-8 etc)

So, it makes no sense to convert them as it stands.
Which means your "20% less disk space" is wrong.

Did you SUM reserved pages for a freshly re-indexed table that is at least 1MB?

If you are using int to store boolean (true or false), and you are converting to Y or N in the char(1), then a more efficient solution is to use bit. Which will take even less space. 3 bit columns will use one byte.

Edit, explanation

SQL Server has very specfic rules about how data is stored, based on datatype and NULLability. This takes into account variable or fixed length data, as well as precision (for decimals).

The issue here was choosing the wide 4 byte int instead of single byte tinyint.

Using tinyint means the data stays as a number, no conversions, and no fancy combine-in-a-single-colun madness. And is the smallest data type possible for this data

Related Solutions

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G

Sql-server – Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

The clustered index "is" the table. Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page. Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online. This means the table will not be accessible during the rebuild operation.

You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables. For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.

Best Answer

Related Solutions

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Sql-server – Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

Related Question