What is the best datatype to use for storing moderate amounts of text in SQL Server (2005)?
For example, imagine a table, storing information on downloads available on a website.
I want a title for the download which is brief, say varchar(20). A path, say varchar(255) then I want to store some user friendly text about the download - a description. In some cases it could be as short as the title, other times you may need a few of paragraphs of explanation.
What's best? A varchar with a size of say 4000, or a varchar(max) or something else?
Chris Roland :
The PAD (Portable Application Description) specification highest character limit for program description is 2000 chars *see Program Descriptions section.\n\nhttp://www.asp-shareware.org/pad/spec/spec.php\n\nDownload site submission tools use this spec to make it easier for authors to submit their software. Also I think there are a couple lists you can download that follow the PAD spec.\n\nSo I would suggest varchar(2000) based on that fact I wouldn't want to store more than I need to. One record would be fine with varchar(max), but when you have thousands the cost of storage might go up.\n\nEdit: varchar(max) can be 2GB - 1, that's why I would limit it.\nhttp://msdn.microsoft.com/en-us/library/ms176089(SQL.90).aspx",
2008-10-15T05:26:02
usman shaheen :
use varchar(max), you can put a huge text in it and its storage size increases as required. you can have sql group functions like having, group by on varchar(max). \nor you can go for text data type if looking for full text search in SQL ",
2008-10-15T05:20:36