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!

  • What are you actually storing, where you can convert int to char(1)?
    – gbn
    Oct 30, 2017 at 13:54
  • 6
    Unless you are storing a very large amount of data, making your table columns agree with a logical model of the subject matter will be more important than trying to save disk space. Oct 30, 2017 at 13:57
  • I would never combine columns just because that might save some space. If that was 20% of a multi terabyte database ( > 10 TB) it might start making sense but even the I would be very hesitant to do it.
    – user1822
    Oct 30, 2017 at 14:47

1 Answer 1


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

  • Thanks for the reply. Every cell of those int columns has a value <10 so two columns were combined into a char(2) column. Oct 30, 2017 at 14:16
  • 3
    @davidzxc574 then use tinyint datatype, which is one byte also
    – gbn
    Oct 30, 2017 at 14:27
  • A single character can actually be up to 4 bytes depending on the character set
    – user1822
    Oct 30, 2017 at 14:41
  • @a_horse_with_no_name not SQL Server, it does not support UTF-8 and the like. char(1) is exactly one byte (ANSI+ code page), nchar(1) is exactly two bytes (UCS-2 encoding).
    – gbn
    Oct 30, 2017 at 15:42
  • A right, I always forget that char and varchar are always single-byte character sets in SQL Server. But according to the manual SQL Server uses UTF-16 nchar and nvarchar which means a single character is stored as either 2 byte or 4 byte
    – user1822
    Oct 30, 2017 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.