MS SQL Server Data Type TEXT or VARCHAR?

In MS SQL Server 2005 two new data types were introduced: VARCHAR(MAX) and NVARCHAR(MAX). These two new data tyoes have the advantages of the old text type: They can contain op to 2GB of data, but they also have most of the advantages of varchar and nvarchar like the ability to use string manipulation functions such as substring().

Also, varchar(max) is stored in the table's (disk/memory) space while the size is below 8Kb. Only when you place more data in the field, it's is stored out of the table's space. Data stored in the table's space is (usually) retreived quicker. So, never use TEXT (or NTEXT), as there is a better alternative: (N)VARCHAR(MAX).

Only use VARCHAR(MAX) when a regular VARCHAR (with length) is not big enough (8Kb).

In DeZign for Databases you still select TEXT and NTEXT but it is better to replace them with the VARCHAR alternative. You can use the Search and replace data types function to replace the data types in your complete data model.