We are in the process of defining a fact table with a couple of fields defined as displayed below:
I suggested downsizing those fields to smallint vs Integer based on the source data max values. Some of the developers want to keep the fields defined as Integer to cover sums and counts in a group by when reporting. Any ideas on the best way to size these fields?
If storage efficiency is a concern, define the base table column with the smaller type. To prevent overflow with the SUM function, you can simply cast the argument to the larger type (INT or BIGINT) when necessary.
I am with your developers - you always remember to do this after the query has failed and it is not always obvious how to recast the correct number in front-end query tools.
Your Teradata Salesman is not going to get significantly richer on a couple of bytes here and there in number fields!
You can make the cast automatic by defining it in a view. Then give your users and front-end tools access to the view instead of the base table.