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
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?