views:

1276

answers:

5

We're doing a lot of large, but straightforward forms for a fairly big project (about 600 users using it throughout the day - that's big for me at least ;-) ).

The forms have a lot of question/answer type sections, so it's natural for some people to type a sentence, while others type a novel. How beneficial would it be to put a character limit on some of these fields really?

(Please include references or citations, if necessary/possible - Thanks!)

+2  A: 

blob and text / ntext are stored outside of the row context, and only a reference stored to the object, resulting in a smaller row size, which will improve performance on clustered indexes.

However because text / ntext are not stored with the row data retrival takes longer, and these fields cannot be used in any comparison statements.

Ady
"these fields cannot be used in any comparison statements." which DBs have this limitation? MySQL and SQLite have no problem... of course, these operations are somewhat slower and indexes have to be length-limited (in MySQL, at least)
Javier
Sorry, I was speaking of SQL server. You can convert back to n/varchar but the expense of doing this, combined with the risk of data loss means that it would not be worth it.
Ady
Which version of SQL Server? It's perfectly acceptable to do in at least 2000 and beyond. It may have been a problem in 7.0, but I don't have a 7.0 test server handy to confirm. The following runs on SQL 2000:SELECT * FROM dbo.My_Table WHERE my_text_column LIKE '%test%'
Tom H.
Nevermind... by comparison I'm going to guess that you mean strict comparators, which do not work.
Tom H.
In SQL Server, CHECK constraints cannot be created on columns of type TEXT and NTEXT.
onedaywhen
+4  A: 

If you have no limitations on the data size, then why worry. This doesn't sound like a mission critical project, even with 600 users and several thousand records. Use CLOB/BLOB and be done with it. I have doubts as to whether you would see any major gains in limiting sizes and risking data loss. That said, you should layout such boundaries before implementation.

Usually varchar is best for storing values that you wish to use logically and perform "whole value" comparisons against. Text is for unstructured data. If your project is a survey result with unstructured text, use CLOB/BLOB

Semi-Reference: I work with hundreds of thousands of call center records sometimes where we use a CLOB to store the dialog between employees and customers.

Josh
A: 

from: http://www.making-the-web.com/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/

There are a few variations of the TEXT and BLOB types which affect size; they are:

Type - Maximum Length -Storage

TINYBLOB, TINYTEXT 255 Length+1 bytes

BLOB, TEXT 65535 Length+2 bytes

MEDIUMBLOB, MEDIUMTEXT 16777215 Length+3 bytes

LONGBLOB, LONGTEXT 4294967295 Length+4 bytes

dl__
+1  A: 

I say, focus on the needs of the users and only worry about database performance issues when/if those issues arise. Ask yourself "will my users benefit if I limit the amount of data they can enter".

I keep a great gapingvoid cartoon on my wall that says "it's not what the software does. it's what the user does".

Bryan Oakley
+1  A: 
joshperry