All Forums Database
goldminer 118 posts Joined 05/09
16 Nov 2011
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?






Jim Chapman 449 posts Joined 09/04
16 Nov 2011

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. 


Jimm 298 posts Joined 09/07
17 Nov 2011

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!

Jim Chapman 449 posts Joined 09/04
17 Nov 2011

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.

You must sign in to leave a comment.