3

I have two tables that are identical and hold the same rows of data.

one uses a varchar(255) for every column of the table, the other is correctly typed for each column (using date, int, decimal etc.).

Using Management Studio's properties window, I can see that the table that stores all data as varchar is far smaller, which is not what I expected to see. My understanding was that an int would only use 4 bytes of space and therefore use much less space than an int stored as a varchar.

What should I look for to understand what is happening here? Both tables have a primary key on an int column and no other indexes. For testing purposes they have been populated with identical datasets from the same source query.

5

So this turns out to be because of the shape of the data I am storing, IN THIS INSTANCE a varchar genuinely takes up less space.

Many of the columns in the table are ints and numeric(38,12) (this is so huge because the data source uses this type). However, many of the values in these columns are 0 or whole integers. As a result a varchar(255) would store this value as 3 bytes (1 byte, plus 2 the two overhead), whilst a int would use 4 and the numeric would use 17. This results in using the correct types to be less storage efficient over the whole table.

To decrease the storage space used, I would need to check if I can change the typing (from int to smallint or numeric(32,12) to a numeric (19,8) etc.) alternatively it looks like sparse columns would be a good solution where there are lots of 0's or null expected.

  • 1
    Also row compression won't take up 4 bytes for an integer if it fits in less. – Martin Smith Aug 10 '16 at 17:55
  • Column store indexes are also appropriate in this instance as this is for a data warehouse - offers incredible data compression and performance. – Neil P Aug 11 '16 at 9:24

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.