4

I have a table (Key tinyint, Value bigint). There are a billion rows for each distinct Key value and only about 100 distinct Key values.

Is there a way to not have to store Key in the every row without breaking this up into multiple tables?

Improve this question
7
  • You want to save storing 1 byte per row? You need some way to identify which key a value belongs to - can you think of some way that would be < 1 byte? I can't...
    – Aaron Bertrand
    Dec 31, 2014 at 22:48
  • 2
    @AaronBertrand 100 gigabytes for the 100 billion rows in this table.
    – John Tseng
    Dec 31, 2014 at 22:49
  • 1
    Still need a way to identify the data. CustomerID is expensive in an Orders table, too, but I don't know a cheaper way to tie those two things together.
    – Aaron Bertrand
    Dec 31, 2014 at 22:51
  • 1
    The only thing I could think of is encoding the key into the BIGINT value - e.g. 1, 1000 becomes 10000000001000 and 255, 789 becomes 2550000000000789 (roughly, tough to tell in mobile how many digits is). But man is that ugly and so not worth it. I would first implement data compression - which will save you at least 4 bytes per row for all values that could fit in int, smallint or tinyint.
    – Aaron Bertrand
    Dec 31, 2014 at 23:10
  • 1
    So the proposed new structure is 100 heap tables. Each heap containing 1bn rows with a single column with a bigint value? What kind of queries do you do against this data? Purely aggregation? Or are there other columns too?
    – Martin Smith
    Jan 1, 2015 at 0:18

2 Answers 2

Reset to default
1

Try leaving it how it is, but using columnstore, perhaps. The effectiveness will depend on the style of usage, but you may find it compresses a lot better, having a useful impact.

Improve this answer
0

You could combine the Key and Value into a dense binary encoding. You'd be better off doing this in the application, most likely. Define the table's column to be binary() of just the right length to hold the packed values. 3 digits (key) + 10 digits (value) gives about 7 bytes for the column (rounding up), down from the 9 bytes needed for a tinyint and bigint. With the Key as the high values the rows will be in the same clustered sequence as currently.

I've little experience dealing with binary columns so indexing, clustering, partitioning and defining good predicates for lookups may prove .. "challenging".

Improve this answer

Your Answer

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

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