Subject Re: [firebird-support] text-type 255 char BLOB vs varchar(255)
Author Ann W. Harrison
vincent_kwinsey wrote:
> I am considering to add new column for table ... There are 2 apparent
> choices:
> or
> VARCHAR(255).

The segment size of a blob is irrelevant. It's an old parameter,
used by the preprocessor as a best guess of a buffer size for
transferring segments of a blob between program and database.
> 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.

The access is likely to be faster but the reason is different.
At some level, possibly masked by the application interface,
reading a blob takes two calls - one to get the record data
and one to get the blob data. However, small blobs are normally
stored on the same page with the data. If you're thinking about
blobs under 300 bytes, the probability is high that the blob
will be on the same page with the data.

There are no significant caching issues with blobs.

Firebird 1.5 allows you to assign text blobs to varchars and back
in stored procedures.

Still, for something that's typically under 300 bytes, I'd use
a varchar. The cost of alter a varchar field to increase its
length is very low, so changing your mind about length isn't
a serious issue. And most programming languages are happier
with varchars.