OnDemand Users Group

Archive => Other => Topic started by: rstockton on December 07, 2010, 09:10:06 PM

Title: Integer and Small Integer in Application Group
Post by: rstockton on December 07, 2010, 09:10:06 PM
On the Field Information screen for the Application Group it allows the use of different types of Integers to be entered as data type.  For searching and space requirements are there any advantages using integers rather than string.

Thanks
Title: Re: Integer and Small Integer in Application Group
Post by: Justin Derrick on December 08, 2010, 02:05:02 PM
Hi Russell.

Integers are "smaller" to store in the database (four bytes in a 64-bit number which can hold a ridiculously large value, versus storing say, a social security number with 11 bytes as a string), which is why dates are stored numerically inside CMOD. 

There are consequences for searching too.  At the very lowest level, comparing integers is a single-step operation for most CPUs.  Comparing strings can take as many steps as there are characters in your field.  Thankfully, today's modern CPUs can do either function in the blink of an eye.

The problem comes down to searching & format.  You can't store the (sometimes expected) hyphens when you store 'formatted' numbers (like a SSN), but you can strip them out at load & query time, and depending on the situation, insert them back in when they're displayed.

When I'm making the decision, I try to think about how people will use the information in the field.  If they're likely to do numerical functions on the fields (query records with Account Balance greater than $100, calculate sum of OverdraftAmount field) then I use an integer.  If there might be queries for string-based wildcards (all account numbers containing "456") then I store the formatted string.

Good luck!

-JD.
Title: Re: Integer and Small Integer in Application Group
Post by: rstockton on December 22, 2010, 02:14:42 PM
Thanks Justin.