I have a database field that needs to store text and HTML tags from a WYSIWYG input box in a web interface.
The text can be a maximum of 2000 characters, but the HTML tags can vary depending whether a visitor uses the WYSIWYG feature and how much he uses it.
I am using MS SQL server, but I feel the question is database independent.
For this field, should I use varchar like the following varchara 4000 (2000 chars plus 2000 chars for possible HTML tags. But HTML tags could be more than 2000 chars depending on users)? Or should I use text or lob data types (which can store unlimied data)?
What is the practical and balanced design (performance, storage, etc.) considering that there will be hundreds of millions of records and high database save and query transactions?
Many thanks!
You should use varchar(MAX)/nvarchar(MAX), which is the successor of the text/ntext data type, since it allows for arbitrary length and does not produce additional overhead. You don't save space by restricting the size of your (n)varchars.
Personally, I would recommend nvarchars over varchars. They take up a bit more space, but you save yourself a lot of trouble once your users start to enter non-English characters.
Note that you cannot create an index on a (n)varchar(MAX) field.