Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
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] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 06:05:35PM -0600, P Kishor wrote: > don't know about other db, but with the ones I have worked, there is > no such thing as unlimited text width field. Oracle maxes out VARCHAR > at 4000 or 8000 (for NVARCHAR).. Of course, I realize, that "unlimited" is just theory. Perhaps I should write: "with no formal limit set" rather. So, I was just wondering, whether (or not) there is a noticeable difference when operating on - let's say - VARCHAR(255), rather than on just TEXT. Never made such measurements by myself. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Fri, Dec 07, 2007 at 05:43:49PM -0600, P Kishor wrote: > > > I believe it used to... fixed width (CHAR) was quicker than VARCHAR > > while the latter was more space efficient, obviously. > > But he didn't compare variable, but limited width (VARCHAR()) - with variable > unlimited width fields (TEXT)? > -- don't know about other db, but with the ones I have worked, there is no such thing as unlimited text width field. Oracle maxes out VARCHAR at 4000 or 8000 (for NVARCHAR).. I guess you could muck around with CLOBs, but as far as I remember, they are a royal pain in the behind. Don't remember about SQL Server. Never worked with DB2. If it matters, Access maxes out at 64k for its memo fields. Puneet. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 05:43:49PM -0600, P Kishor wrote: > I believe it used to... fixed width (CHAR) was quicker than VARCHAR > while the latter was more space efficient, obviously. But he didn't compare variable, but limited width (VARCHAR()) - with variable unlimited width fields (TEXT)? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
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...)? > -- I believe it used to... fixed width (CHAR) was quicker than VARCHAR while the latter was more space efficient, obviously. One of my Oracle-ish friends was telling me that now it really doesn't matter as they are all very fast, and he just makes everything VARCHAR. Some db, perhaps under some installation conditions, will fail the operation if you try to insert a bigger string in a CHAR column. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
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...)? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Any advantages of "varchar()" over "text"?
Zbigniew Baniewski wrote: We can define in, using SQLite, both "fixed max. width" VARCHAR() fields (I know, in practice it can be crossed over), as well as "no limit" TEXT fields. 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: internally, there is only one string type and it supports strings of unlimited length. The length specified in VARCHAR() spec is ignored. For more details, see http://sqlite.org/datatype3.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
