Memo vs. Text 255

[Anthony Bollinger]

I have taken over a database with a Memo field in it. About one-fourth of
the records have data in the Memo field. All of the field lengths are less
than 255, with 75% of them being under 100 characters. What are the data
storage specs for Text and Memo? Is text just a fixed-size block? I am
wondering if it makes sense to stay with memo, or if going with text 255
will have any advantages?

Many thanks,
Tony

 

[Jamie Collins]

I am
wondering if it makes sense to stay with memo, or if going with text 255
will have any advantages?

Fixed or variable? UNICODE or ASCII? It's traditional to choose data
types and sizes basic on the nature of the attribute on the reality
being modelled. Off the top of my head I can only think of
email_address as being VARCHAR(255). Apologies for being blunt but I
think you need to do some research. Here's a starter/hint: if it's a
person_family_name then I use VARCHAR(35) to match the jurisdiction's
governmental schema
(http://www.govtalk.gov.uk/gdsc/html/frames/PersonFamilyName-2-0-Release.htm).

MEMO is blob data, stored differently and involves an extra level of
indirection for storage/retrieval. Consequently, there are limitations:
only the first 255 characters used in indexes, not able to create
joins, etc. On the other hand, do you have justification to introduce
risk by reengineering the design?

Jamie.

--

 

[Jeff Boyce]

Anthony

If I remember correctly, Access doesn't use a fixed length block for text,
but stores as many characters as entered, up to whatever limit is set. It
may be, however, that setting a small limit (e.g., 2 text characters to hold
State abbreviation) reduces the overall size, I'm not sure.

If your only concern is storage specs, consider the potential extra work
Jamie points out from converting the data type. Have you checked Access
HELP on "specifications" to see storage specs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

A text field of 255 does not use up 255 characters for each record. Access
doesn't waste space like that.

Memo fields have certain limitations such as sort and grouping plus other
problems. One of these problems is corruption issues. Memo fields are much,
much more likely to corrupt than a text field and when they do, compacting
and repairing is less likely to fix the problem.

IF (big IF) the users will never, ever need to put more than 255 characters
into this field, I would convert it to a text datatype.

Oh! Check the lenght of all the other fields, including number fields, in
the table first. Access has a limitation of 2,000 characters per record,
excluding memo and OLE fields. It's possible that the designer thought that
the 100 or so characters in this field may put the record over that
limitation and decided using a Memo field was a good compromise.

 

[David W. Fenton]

I have taken over a database with a Memo field in it. About
one-fourth of the records have data in the Memo field. All of the
field lengths are less than 255, with 75% of them being under 100
characters. What are the data storage specs for Text and Memo?
Is text just a fixed-size block? I am wondering if it makes sense
to stay with memo, or if going with text 255 will have any
advantages?

Others have explained various aspects of the question. One that
hasn't been mentioned is that memo fields are *not* stored with the
main record. All that is stored in the record is a pointer to the
data page where the memo data is stored. This means that memos are
somewhat more fragile than other data types, because the pointer can
get broken fairly easily.

I would only use memo fields where they are definitely required.

 

[Jamie Collins]

One that
hasn't been mentioned is that memo fields are *not* stored with the
main record.

Well, I tried to when I said they are "stored differently and involves
an extra level of indirection for storage/retrieval."

I know, you don't haveto say it ;-)

Jamie.

--

 

[Jamie Collins]

A text field of 255 does not use up 255 characters for each record.

That depends: 'text' is a bit vague e.g. this

CREATE TABLE Test (
fixed_text_col NCHAR(255) NOT NULL
)
;

will always store 255 (UNICODE) characters, padding with spaces to the
right if necessary.

Jamie.

--

 

[Guest]

True. I can do the same thing in Oracle using CHAR; however most Oracle
developers now use the VARCHAR2 datatype. In a similar vein, most Access
people use the default tools for table design to create fields with the text
datatype.

 

[Jamie Collins]

most Access
people use the default tools for table design to create fields with the text
datatype.

Consequently, the most popular size text column is 50, thus proving
that the OP is not a member of the set {most Access people} ;-)

Jamie.

--