Subject text-type 255 char BLOB vs varchar(255)
Author vincent_kwinsey
I am considering to add new column for table (which store attribute
values for some dimension - in the terms of star-shaped OLAP design)
which will store data like desciption or note (i.e. data that should
be shown to end-user for each of retrievede record from this table -
because the pk lacks some meaning to end user). There are 2 apparent

There are some good pros for varchar:
- it is easier manipulate varchar than blob (both from SQL and from
Delphi code), it is easier to transfer data from one DB to other -
using plain text SQL scripts
- the access is exepcted to be faster - because - when record is
retreived then the values is taken from the same data page, as
opposite - when blob is retrieved then at least 2 pages always will be
read - one for record and another for blob value of this record. But -
does this can be an argument if this table is containing no more than
500 records but fact table which references it can contain several
million records and can present even several thousand of them to end
user? I expect that blob can be cahced - so - overhead will not be so
- any manipulation with BLOB field in the code of procedures/triggers
language will involve use if UDF to cast BLOB to varchar and back, so -
using varchar will not impose any overhead of UDF. However - use of
UDF is so optimized that it can be hardly named as overhead.

but there are some pros for BLOB as well:
- in case when it requirements changes and stakeholders are requesting
to store longer data in field - it can be more easily to make larger
BLOB field than to varchar. But - maybe this is not an argument -
because - as I remeber one post - then someone has used even varchar
(10'000) before move to tex-type BLOB's

What are Your thought regarding such a decision? What else issues
should be taken into account to make the best design?