Best datatype for SQL table with lots of long text fields

Question

Hi,

I have the following table in a SQL Server 2008 database with lots of long varchar fields for text descriptions. I want to know what is the best datatype to use for these. Currently I'm using varchar with 2000 character limit but this might cause problems if people need to add more text. Is there a problem using TEXT more than once in a table? What do you suggest?

Table
 name varchar(250)
 description1 varchar(2000)
 description2 varchar(2000)
 description3 varchar(2000)
 description4 varchar(2000)
 description5 varchar(2000)

Answer 1

To my mind, there's a problem with using TEXT at all -- it's deprecated!

Instead of using VARCHAR(2000), just use VARCHAR(MAX) -- that gives you all of the size of a TEXT column, and you'll be able to manipulate it just lie a VARCHAR(...) column.

Answer 2

For SQL Server 2008, I believe the recommended practice is to use varchar(max) rather than text - text is to be deprecated, I believe, in a future SQL Server edition.

Your table design seems sound enough to me, but I suspect some will suggest that if the columns are filled to the max - and the table row count is large - then there might be some performance issues. SQL Server can only store 8K per database page, so it would have to split our your table - which has a performance impact.

Answer 3

Another benefit of using varchar(MAX) is that the fields will not count against your 8K maximum row size - since each of these fields can be larger than 8K by itself, SQL will store them outside the data page as soon as necessary, so all that is stored in the page is the pointer to the data.