CODEWITHSUNDEEP
sql-servercastingvarbinaryvisual-foxpro
Jeffrey Clarke
Jeffrey Clarke

Reputation: 37

What is the best column type to store mixed data in SQL Server

We are doing a Visual Foxpro (DBF) to SQL Server conversion, but will retain the VFP GUI to now use the SQL Server database.

In several memo fields in VFP, we store a mix of ASCII characters and text.

What would be the best column datatype to store these values preferably without having to CAST?

Along these same lines, we also have times when we convert Word documents into a Memo file, for these, which field type in SQL Server would work the best?

Thanks for your help.

Upvotes: 1

Views: 3565

Answers (1)

Andrew Rosca
Andrew Rosca

Reputation: 51

It depends on the version of SQL Server you are using. 2005 and newer support VARCHAR(MAX), which can store strings up to 2GB. Short strings are stored efficiently (in the row) while larger strings are automatically stored in a blob database area, with only a pointer to the blob stored in the row. From a developer's perspective you don't need to worry about this complexity and use VARCHAR(MAX) if your data can exceed 8000 characters.

As for your Word files, it depends if you want to store the actual file (in binary format) in the database or only the content. In the first case you could use VARBINARY(MAX) (or store the file on disk and only the path and other file meta data in SQL Server). If you want to store the actual content you will need to first convert to some suitable format (Rich Text, XML, etc.) and then store that in VARCHAR(MAX).

NOTE: use NVARCHAR instead of VARCHAR if your data contains unicode characters (rather than just ASCII)

Upvotes: 3

Related Questions