A multi-billion-row fact table in our database has 10 measures stored as int
columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint
. To save I/O, we're investigating whether it's practical to store these columns as smallint
instead of int
.
But we're concerned about what problems might crop up from doing this, including:
SUM(SomeSmallInt)
will frequently overflow, so many queries would have to be rewritten to cast these smallints to ints before aggregating, e.g.SUM(CAST(SomeSmallInt as int))
.- This table is read in many places, so changing the data type of these columns might involve a lot of review, change, and testing.
So we're wondering if there's a lower-cost solution that would store as smallint but expose the colunms as ints to readers. Like this:
- create smallint "storage" columns that the table's (only) writer will use, but otherwise no other client will care about.
- to support existing readers, create int-typed computed columns that are the same names as the original int columns.
What are the pros and cons of this approach? What can go wrong? Is there a better way to reduce storage & I/O without causing problems with overflow and requiring existing readers to be rewritten?