In general you want to stay away from text data type in sql server. It requires a lot more processing to store the data. Basically, if the "text in row" option is on, data is under a certian size (default of 256 bytes), sql server will store the data in the row, if the option is off or the data is > than 256 bytes, it will store a pointer to a different part of the file that holds the actual data (similar to foxpro, memo fields require another file on the disk). If you know your memo fields will never be bigger than 8000 varchar or 4000 nvarchar characters by all means, use a varchar then. That way sql server will alway store the data in the row and will make everything faster.

More info is in the BOL. Just search around for text data type.


>I know the foxpro upsize wizard converts the foxpro memo fields in to a text data type in SQL, but is this the best match. I belive that text data fields are only 4000 + characters. Is that correct? My new project has alot of memo fields in the data structure and Iam planning to move it into SQL 2000. What special problems will I have with storing and searching and updating foxpro memo fields and what is the best SQL data type to use. Thank everyone so much ????