Convert MS Access Memo Field to SQL Server

I have a database that I am moving to SQL server and one of the tables is using a Memo field.  I have been told that we have limited space on the Server so I am trying to be mindful of the data types in SQL server.  I am looking for suggestions on what the data type should be for an Access Memo field.

It has been suggested to use either text or varchar for the field in SQL server.  Is there a way that I can query the current table to figure out the longest number of characters in the MEMO field.  If so can you please provide assistance with it?
Who is Participating?
Paul Cook-GilesConnect With a Mentor Access/VBA/SQL Database DeveloperCommented:
Build a query on the table, and add this column:  MemoFieldLength: Len([MemoTypeField]).  When you run the query, that column will tell you how many characters there are in the memo field for each record.  If you sort the query by that column, descending, the highest number will be at the top, and you'll know how big your VarChar field needs to be.

Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
BTW, your SQL field type should be VarChar, not Text... Varchar will use only the amount of space necessary to contain the characters in your field;  Text will use the specified amount of space, whether it's used or not.

Mark WillsTopic AdvisorCommented:
It translates to IMAGE or TEXT or if not really binary and just textual, then can use varchar - in fact could be varchar(max) which allows up to 2 gig, but not so sure it will convert "cleanly" (dependant on content).
bluefeet10Author Commented:
Thanks for the help!!
All Courses

From novice to tech pro — start learning today.