Field type VARCHAR vs TEXT

[MarkButler]

Is there a performance or other advantage to using VARCHAR over the TEXT field definition?

I am converting from a FoxPro database which has a MEMO type field. It would be easy to say for any MEMO field in FoxPro to just define it as TEXT to SQL. In most cases the maximum characters used in FoxPro is less than the 8000 VARCHAR limit. What would be a good rule to determine the correct field type?

TIA
Mark

 

[clapag22]

Only use text if you know the field will contain more than 8000 characters. Text reduces performance and many string functions are not available on text columns.

Bottom line, only use it if you need it.

 

[MarkButler]

With that said is there any penalty for using VARCHAR (8000) on all my memo fields?

Or should I go through each one and find a maximum used and set it to a more reasonable number?

 

[mrdenny]

Keep in mind that the max record length is 8060. If you have more than one VARCHAR (8000) field in a table, and you try to put 8000 characters in each field, you will have your data truncated or it will error out.

Denny

--Anything is possible. All it takes is a little research. (Me)