SQL Server Column types for lots and lots of text

Question

I'm working on a project which replaces a piece of paper, and on that piece of paper, there are essentially two kinds of text fields. One is 10-40 characters and works well with varchar, but the other is a big box with "attach additional sheets as necessary" at the bottom of the box. So, we're talking a lot of text.

What are the advantages and disadvantages of different data types I can use for this? Varchar seems to hit up against a 4096 row size limit. Should I be using the text type? Or should I just break it into pieces myself and place it in a subtable?

SQL Server 2005 currently, but I can upgrade if there are compelling advantages.

Answer 1

In SQL Server 2005 onwards, you can use VARCHAR(MAX) (or NVARCHAR(MAX) for unicode). These can hold as much data as TEXT and NTEXT respectively which will be deprecated in the future. VARCHAR(MAX) is much easier to deal with than TEXT and doesn't have the same limitations with how to interact with it.

So without a doubt, don't use TEXT. For that much data, VARCHAR(MAX) is the better option.

Answer 2

With SQL Server 2005 and up, I'd suggest you use VARCHAR(MAX).

It's up to 2 GB in size, and yet you can still use all the usual T-SQL string functions on it.

Marc

Answer 3

Use VARCHAR(MAX). This is what it's for. Maybe even NVARCHAR(MAX).

Answer 4

There's [n]varchar(max) (the replacement for the old [n]text) for really big amounts of text.

Answer 5

Check here for a good answer: http://stackoverflow.com/questions/564755/sql-server-text-type-v-s-varchar-data-type

Answer 6

Since Microsoft has plans to deprecate the text, ntext, and image datatypes, I would recommend the varchar type with a size limit of max.