On 10/28/2017 8:58 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2017-10-28 07:17, Charlie-gm wrote:
Whatever the case, please don't do something idiotic like declare "VARCHAR(4000)" for fields that will never have more than 20
...
I don't do that but I thought using varchar(n) fields were supposed to be space-efficient in a sense that it just used a extra 1 or 2 bytes up front at the head of the record to identify the true length? It's not really allocating the space like a CHAR(n) field would...right?
The main problem is the database engine, and anything that has to allocate memory from data from the database engine, has to constantly assume the field size could jump up to 4000 (or max length, etc), at any time.
While space is not immediately allocated on disk, memory, etc, the engine has to assume it will be at any given instant. Some of them do "guesses" and watch "utilization" of fields, etc and will allocate chunks according to those guesses (recall that most DB servers do not just allocate disk space on each row/field update, they do it in chunks). But it's a waste of processing power and disk storage compared to knowing your data and doing even just a little data analysis.
I've seen "enterprise" app databases that get up to 1TB in size, and the dev team says "we need more disk space!!! we're running out!!!"... I then, took that database design, did a little database analysis on it, and got it into about 10GB of VFP tables (including indexes). Of course, that does not account for engine transaction logs, etc. But those were regularly purged (data archives were kept on different servers altogether). In the DB Server design, I saw varchar('max <whatever that value is>') all over the place. Now this was an Oracle database server, so there may be some differences between engines.
So while some claim that varchar will only use up disk space (and memory) based on the number of characters used, in practice that is not completely accurate. And while char can also be inefficient if too large for what is used, it will "scale" at a predictable rate and require less "processing" burden on the engine.
And, of course, there is a ton of additional "theory" and discussion about this across the web, so you may want to do more research.
-Charlie