Text data type field size

[Stephen @ ZennHAUS]

Hi guys and gals

Just wondering. From a storage perspective, if I have a field that has the
data type Text with a size of 50, does Access actually allocate storage
space in each record for all 50 characters if there is nothing in the field
or if the data in the field is less than 50 characters long?

Thanks

Stephen @ ZennHAUS

 

[Jeanette Cunningham]

No, access stores the characters without any trailing spaces.
Limiting the field size is useful for the row in the table.
There is a limit to the number of characters in an entire row in a table. I
don't have the figures with me at the moment, but the number of characters
allowed in a complete row of a table is fairly large - however if your table
has a lot of fields and users do put 255 chars in every field in the same
row, you could theoretically reach this limit.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Stephen @ ZennHAUS]

Thanks Jeanette

Don't think I'll ever reach such a limit. Shudder to think how much data
that would actually be. Poor data entry person.

I was just checking because I wanted to ensure that I was not unnecessarily
consuming space by having say a length of 50 when I only needed 25 or
something like that.

So thanks for the answer. I feel more comfortable now.

Stephen @ ZennHAUS

 

[John W. Vinson]

No, access stores the characters without any trailing spaces.
Limiting the field size is useful for the row in the table.
There is a limit to the number of characters in an entire row in a table. I
don't have the figures with me at the moment, but the number of characters
allowed in a complete row of a table is fairly large - however if your table
has a lot of fields and users do put 255 chars in every field in the same
row, you could theoretically reach this limit.

2000 characters, FWIW. It's a nasty error since you can have a table with
(say) ten 255 byte text fields, and use it for years - and then some verbose
user can trigger the cryptic error message.

 

[a a r o n . k e m p f]

good stuff-- sounds to me like a problem with Jet

on the other hand; SQL Server allows 8096 bytes per row
and you actually will get an informational message when you add
columns to a table that would allow it to have more than 8096 bytes in
that row (not including text (memo) datatypes, etc)

 

[Larry Linson]

With UNICODE compression "on", the 4096 byte maximum may hold more than 2048
characters (less control structure), as many languages' alphabets are
compressed from 2-byte Unicode into 1-byte, just like the "olden days".

As far as I know, all text, memo, and OLE object Fields are stored as
variable length so you can define all your Text as 255 characters and use no
more/less space than if you defined it as 50 characters. I think, maybe,
that was the original question.

Larry Linson
Microsoft Office Access MVP

 

[austris]

There is a limit to the number of characters in an entire row in a table. I
(..)
Could you please shed some light on how this works then with memo
fields that have like 65K limit? Does that mean that whenever there is
a memo field - it gets split by db into multiple rows internally as
the 65K (a single field in a row) is way over the 2K limit (per entire
row with all the fields in it) mentioned above...?
I must be confusing something.

 

[John W. Vinson]

Could you please shed some light on how this works then with memo
fields that have like 65K limit? Does that mean that whenever there is
a memo field - it gets split by db into multiple rows internally as
the 65K (a single field in a row) is way over the 2K limit (per entire
row with all the fields in it) mentioned above...?
I must be confusing something.

Sorry... should have added that. The 2000 character limit does NOT include
Memo or OLE fields. A Memo field contributes (IIRC) 18 bytes to the record
size limit, regardless of the size of the memo. Note also that - if it's
filled programmatically - a Memo has no size limit other than the 2GByte total
database size.

 

[Albert D. Kallal]

Could you please shed some light on how this works then with memo
fields that have like 65K limit? Does that mean that whenever there is
a memo field - it gets split by db into multiple rows internally as
the 65K (a single field in a row) is way over the 2K limit (per entire
row with all the fields in it) mentioned above...?
I must be confusing something.

The memo fields are separate, and they are simply linked as a big blob of
data and placed in a different location

So, memo fields are NOT part of the 2k limit (except for some small internal
overhead...apparently 18 charaters as John as pointed out).

 

[austris]

Thanks for the explanation!
Another day - another dollar!

 

[a a r o n . k e m p f]

of course, SQL Server has _MUCH_ better datatypes than MS Access.
SQL Server can have 8092 kb per page

and 'text' fields in SQL are the same as memo fields in Jet.
Also- SQL Server now has 'varchar(max) fields' that make life a lot
easier.

Jet is dead-- you really should move to SQL Server if you have
anything _REMOTELY_ close to 2000 per row

-Aaron

 

[Tony Toews [MVP]]

Jet is dead-- you really should move to SQL Server if you have
anything _REMOTELY_ close to 2000 per row

Jet is alive and well despiet Aaron's pronouncements.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/