Creating a new text column in a SQL Server Table: which type should I choose?

Question

I have a text field that can be 30 characters in length, however it can achieve around 2.000 characters.

For "future safety", it would be nice if this new column could support until 3.000 characters. I think the average size of this field is 250 characters.

Which SQL Server 2000 data type is better to increase performance and disk space?

Answer 1

If your field is going to hold up to 3000 characters, you should probably make sure your datatype will allow for something that big. Design based upon your maximum, not your average.

Answer 2

Do you need unicode or nonunicode data? Use either varchar(3000) or nvarchar (3000). Don't use text or ntext as they become problematic to query or update.

To design for average length makes no sense.

Answer 3

This sounds like the perfect case for VarChar. Are you doing any indexing on this field?