What kind of DataType should be used in a column that usually will store a short string (between 10 and 40 characters) but eventually will store a large amount of text (500 - 1000 characters)? The table that holds this column has only three columns. Two of them make a composite key and the other is the column in question. I will not have to sort or order based on this column.
So, what DataType should I pick?
Thanks in advance. Sorry for my english.
It's a toss-up between VARCHAR (1000) (you might need it to be greater than 1000), and just using TEXT. I don't know whether you require NOT NULL, but obviously add that if you need it.
Note that if you make it VARCHAR, you're imposing a definite limit on column size. Use VARCHAR only if you know for sure you won't go over that size. If you might have outliers, just go with TEXT.
I'm still kicking myself for choosing VARCHAR when I could have used TEXT.
For example, I had a table for phone numbers:
number VARCHAR(50) ,
description VARCHAR(100) ,
Normally "description" is just something like "cell" or "home", but sometimes you want to type in something like "Sister's cell phone, do not call in case of emergencies because she works nights!!" -- then you're up a creek.
If you use VARCHAR, I would put in a max amount that is way above what you imagine of anything that might go there, and even then, you can't anticipate all use cases. Additionally, you still have to write code handling code for instances when the input text length is greater than the max allowed length.
Why put a constraint in when you don't have to? Why write the handling code if you don't have to? Is the performance gain really that much?
