Link to home
Create AccountLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

Memo field or text with 255

Experts,

Is there a performance difference between having a field with either a text with 255 character limit or is a memo field better than text with 255?  I needed to increase the character size of a field that was already at 200 and I bumpted it to 255 but now am thinking why not just make it a memo field but maybe there are some reasons for not doing this that experts know.  
thank you.
ASKER CERTIFIED SOLUTION
Avatar of Bardobrave
Bardobrave
Flag of Spain image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mbizup
Other considerations -- memo fields have some inherent issues that text fields do not.

-  You can't join on memo fields
-  Aggregate queries (MAX, MIN, GROUP BY, etc) involving memo fields will return truncated values
-  Calculations/derivations based on memo fields fields in queries will return truncated values

There are probably more, but those immediately jump to mind.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of pdvsa

ASKER

ok I think I will use a text field and not memo...thank you for the advice!
Avatar of pdvsa

ASKER

I thought a split is best.  Let me know if not...
The bottom line is the more "characters" you give the user, the more they will use.
;-)
So if you use a Text field, tell them that the hard limit is 255.
Then when they say they need more, ...ask why the text cannot be simplified or shortened?

So just for reference, can you give us some idea of what type of data you are storing in this field?
Remember 255 characters is over four sentences worth of text.

As you know, may apps/websites have a limit on the number of character in a field.
(Some even display a dynamic "character counter")

Because of the reasons mbizup mentioned above, I almost never use a memo field.

Obviously every situation is different, just my thoughts on this issue.

;-)

JeffCoachman
One issue you may encounter in the future IF you use a memo field:
The new "Rich Text" option (for memo fields) will store the HTML formatting strings IN the field.
So a simple string like "JetSki", *With absolutely No formatting" will be stored as 17 characters instead of just 6!

SELECT JunkMemo, Len([JunkMemo]) AS Expr1, Len(PlainText([JunkMemo])) AS Expr2
FROM YourTable;

So these formatting characters will silently eat into your total character count.

JeffCoachman