2

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 '17 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. – Walter Mitty Oct 30 '17 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. – a_horse_with_no_name Oct 30 '17 at 14:47
3

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. – davidzxc574 Oct 30 '17 at 14:16
  • 3
    @davidzxc574 then use tinyint datatype, which is one byte also – gbn Oct 30 '17 at 14:27
  • A single character can actually be up to 4 bytes depending on the character set – a_horse_with_no_name Oct 30 '17 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 '17 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 – a_horse_with_no_name Oct 30 '17 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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