Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Using larger data type to avoid sum overflows?

3 posters

Go down

Using larger data type to avoid sum overflows? Empty Using larger data type to avoid sum overflows?

Post  AaronLS Wed Mar 30, 2011 6:40 pm

In SQL Server, there is the potential that a sum of an int column in a fact table may result in an overflow, depending on what attributes are being grouped on. In all queries we could cast the column to bigint beforehand, but I am considering if it is just better to go ahead and declare the column in the fact as the larger data type. This would basically trade space for ease of use and also avoid bugs that might occur, because without it someone might forget the cast to the larger data type when writing future queries. Wondering if anyone else has encountered/considered this and what your reasoning for which decision you made was.

Thanks, take care.

AaronLS

Posts : 8
Join date : 2010-02-24

Back to top Go down

Using larger data type to avoid sum overflows? Empty Re: Using larger data type to avoid sum overflows?

Post  BoxesAndLines Thu Mar 31, 2011 1:02 pm

Make the column in the database as big as needed. Requiring users to cast the column on every query doesn't make sense.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Using larger data type to avoid sum overflows? Empty Re: Using larger data type to avoid sum overflows?

Post  hang Thu Mar 31, 2011 9:00 pm

BoxesAndLines wrote:Make the column in the database as big as needed. Requiring users to cast the column on every query doesn't make sense.
Agree! And also the fact tables are supposed to be as concise/normalised as possible for the purpose of space saving and performance. So having any over-kill data type runs against that principle.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

Back to top Go down

Using larger data type to avoid sum overflows? Empty Re: Using larger data type to avoid sum overflows?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum