Numeric sizing when using aggregation

Database
Enthusiast

Numeric sizing when using aggregation

We are in the process of defining a fact table with a couple of fields defined as displayed below:

new_hire_cnt   Integer

transfer_cnt  Integer

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?

Thanks,

Joe

3 REPLIES
Enthusiast

Re: Numeric sizing when using aggregation

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. 

Enthusiast

Re: Numeric sizing when using aggregation

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!

Enthusiast

Re: Numeric sizing when using aggregation

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.