I have been looking for a good answer regarding which Data type to use when designing a new table that would contain a comment field. I searched books online and Google but couldn’t find a good answer. Users want to be able to put long comments in this table. But I don’t want to specify a Data type that would waste space. We’ll be adding “comment” field to few other tables. So, I just wanted to know which data type would be more efficient. By the way, we’re currently using SQL 2005 and will be moving to SQL 2008 soon. My choices are
Text: What is the maximum number of characters allowed for 'Text' Data type
Ntext: I found that ntext is no longer supported as string function? Is this true.
Or just use varchar(n): I could just use varchar and specify number like 1000 to allow a long string.