What is Verbinary, where it is used? & Which Datatype should I choose for serial no, cash, and Id's? 2

[Saurabh.hom]

In light of excerpt from

http://www.dfpug.com/loseblattsammlung/migration/whitepapers/VFP_PER.htm

"Use New Data Types:

We introduced four new data types to Visual FoxPro: DateTime, Integer, Double, and Currency. All data that use these types are stored as binary data on disk (Integer is a four-byte binary value; the others are eight-byte binary).

There are two advantages here. First, because the volume of data stored on disk is smaller (an eight digit number stored as Numeric takes eight bytes, whereas it only takes four bytes if it is stored as Integer), loading data and indexes into memory from disk is faster because more actual data can be loaded into memory pages. The second advantage is that no data translation needs to occur. Internally, Visual FoxPro represents integers as four byte binary values and has to translate numerics, which are stored as ASCII on disk. So, every read has to be translated from ASCII to binary and back again. With Integers Doubles, DateTimes, and Currency, such translation doesn't occur so data access is faster.

Of the new data types, Integer is the most important for speed. Wherever possible, use Integer data types for primary and foreign key values. The benefits will be smaller DBFs, smaller indexes, and (perhaps most importantly) much faster joins! "

I want to know which should I choose between interger, double and currency. When at present I use numeric for representing serial no and any data involving cash, or ID. ?
Please also tell me about Verbinary and where to use it?

 

[Mike Lewis]

Forget about how the data is stored internally. Just focus on how you will use the data.

Integers should be used for numeric data where the value is a whole number and lies in the range (very approximately) plus or minus two billion.

If you are dealing with numbers with a fractional component, or those that lie outside that range, use double.

As for currency, I don't know of any compelling reason to use it.

Varbinary is similar to a blob, that is, arbitrary binary data of some kind. It is not directly concerned with storing numbers.

When it comes to choosing a data type for an index key, integer is by far the most efficient.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Integer is a very old type, so you came across a very old document here, maybe about VFP3? 1995 is mentioned, maybe this is about VFP5, notice there was no VFP4, so I'm guessing between two successor versions of VFP, which both are more than 20 years old. That said many things hold true forever, but you should probably search for something more recent to find best practices for data storage in VFP9 tables.

You typically decide as Mike suggests, for the type of fields that is having a range covering your needs. As VFP has no byte or word type for lower range ints, you could also use Q(1) and Q(2) field for that, but will have the burden of converting from a numeric variable to Q(1) or Q(2) and back, so you also store such low range integers in int fields. We're far away from times, where saving bytes is of the essence.

Tha major reason for data types should be about best capabilities to process data, human readability is not the concern of DBFs, it is the concern of forms and reports. So, for example, you don't store dates in char fields, you rather make date display settings (SET DATE, SET HOURS, SET MARK or simply SET SYSFORMATS to get display as set up in Windows) and use date or date time types. Only storing datetimes in datetime fields will allow you to easily determine working time by subtracting workedto-workedfrom time intervals. The intervals are in seconds and can then easily be turned into minutes and hours.

Mike says currency has no compelling reason to be used. Indeed you have to SET CURRENCY to get other currency symbols and don't gain much. Currency is a little more precise than double float, but I would perhaps rather store cents in integers or floats, if it comes to money, currency would help to better map to MSSQL money type. And that alone is a good reason to choose types, also in many other cases, varbinary and blob were adapting VFP to MsSql types, which existed quite a long time before VFP introduced them. Look into help-topics about types, also online help for T-SQL and you find usages of each type.

For example, most often serial numbers are not used in calculations and are longer than 7 digits, therefore char fields are best suited instead of going float/double with them. Also when you have letters inside, of course, but not only then, both their length and their usage makes char fields the best type suited for them. So also always think about data usage.

Bye, Olaf.