"John Smith" <jo********@microsoft.com> wrote in message
news:JG********************@news.siol.net...
What is the difference? Is LONG VARCHAR only 28 bytes longer then VARCHAR?
Is this the only difference?
Originally VARCHAR was limited to a length of 255, so LONG VARCHAR was
needed, but obviously they both support about 32K now (VARCHAR supports 28
bytes less as you mentioned).
One big difference between VARCHAR and LONG VARCHAR is that LONG VARCHAR is
stored in a separate area like a LOB (CLOB, BLOB, etc) and also like a LOB,
does not use bufferpools, so every select, insert, update, or delete of a
LONG VARCHAR requires direct disk I/O, just like LOBs.
In order to use the 32K bufferpool for VARCHAR, they had to make the maximum
size 28 bytes shy of 32K (although you may have to make it even smaller to
ensure your entire row fits in a 32K page, accounting for page and row
overhead)..
For this reason (and because IBM will be phasing out LONG VARCHAR in a
future release), you should not use LONG VARCHAR on any new development.