On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Fri, Dec 07, 2007 at 05:49:56PM -0500, Igor Tandetnik wrote:
> > >So I would to ask: are there any benefits from using VARCHAR() and > > >not TEXT? > > There is no difference whatsoever to SQLite. It maintains the types for > > compatibility with other DMBS only > Just from curiosity: perhaps could you tell, does it make any differences > (other than just coercing a limit) in case of other database systems (like > f.e. PostgreSQL, or other known to you)? > > If so - is it significant difference (in data access speed, or any other...)? It varies wildly depending on the database and how its storage was designed. In PostgreSQL, there is no difference; VARCHAR is just TEXT with a constraint on length. However, its storage subsystem is such that any data of variable length is stored the same way, up to a maximum of 1GB, and all variable-length data types are built on that. Firebird and Interbase have a 32KB limit on VARCHAR size (the limit you use may be lower depending on encoding selection), but it's stored essentially the same way as other database fields. It does not have a TEXT type, so your next option is BLOB (with a subtype label of text), which is stored separately from other data. It's not entirely transparent due to the way it interacts with Firebird's transactional architecture, so there are some caveats to using it. That's beside obvious disadvantages of it being a separate type, and therefore not as easy to use string manipulation functions with. And so on... In general, you probably wion't find any significant difference between a TEXT type (if it exists) and a VARCHAR type. As another reply mentioned, the major historical difference was between CHAR and VARCHAR, since the former can be optimized based on a fixed-length architecture. You're less likely to see that now, since storage and processing capabilities have changed such that it's better to spend more CPU time in an effort to make the on-disk data as compact as possible, since storage is so slow to access. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
