Handling performance and using data types

0
votes

I have some web forms that contain approx 100 to 150 text fields. So, in most of the fields a user can enter a great amount of string data. I had previously entered VARCHAR(150), which will not be large enough.

So, my question is, which is better to use: VARCHAR(MAX) or TEXT? I am using SQL Server 2008 R2.

There are many related questions available on Stack Overflow, but I am still confused. Assuming web forms with more than 50 fields with the TEXT datatype, will that cause any performance-related issues, or make our DB very large? I thought that VARCHAR(MAX) could store up to 8000 characters maximum, but I have some fields that can have more than 8000 characters.

Please guide...

1
sqlsql-serverdatabasesql-server-2008tsql
"web forms that contain approx 100 to 150 text fields" o_O - zerkms
If you have fields that can be over varchar(max) limit, then you don't really have a choice, right? - Sergio Tulentsev
But Is it right that varchar(max) can store upto 8000 characters only? - Sweetie
@Sweetie No, that's not right. From here on BOL: "max indicates that the maximum storage size is 2^31-1 bytes" - Doug_Ivison
@Seetie then, sir, you have never bothered to even read the documentatin. WHich is a baseline for a developer who wants to do a good job. And this leads likely to all kinds of delusions about what your tools can do, which will lead to really bad programs. A PAGE can be 8000 bytes long - rows, in total. But varchar(max) only stores a token there, with the actual text stored in other pages. Please read the docuementation on this stuff - and yes, you will ahve performance problems with 150 text fields. I doubt the requirements. - TomTom

1 Answers

2
votes

If you're using SQL Server 2005 or newer use varchar(MAX). Text datatype is deprecated and should not be used for new development work.

see also: http://msdn.microsoft.com/en-us/library/ms187993%28v=sql.90%29.aspx

To answer your extra questions in the comments: 8000 is the maximum you can enter in a default varchar(x) type. This is because you can get maximum 8000 characters in a 'data-page'. But with varchar(max) you can store up to 2^31-1 bytes as @Doug_Ivison allready mentioned. Consequently as soon as the amount of characters is greater than 8000 you'll need multiple pages to contain your data. The same is valid for a 'text' type. It also will need multiple pages for this amount of data. So please don't use text. Besides the fact it's deprecated it doesn't support the things varchar(max) does, like ability to search & index.