Re: max no of column with datatype LONG

http://groups.google.com/group/comp.databases.informix/browse_thread/thread/2 e0279ed33ec18b4/350cf88479eb985c%23350cf88479eb985c?sa=X&oi=groupsr&start=0&n um=3 Above link has following reply by Jonathan Leffler: Well, the row size is limited to 32767 bytes. The minimum size of column is CHAR(1). So, you could in principle have 32767 different columns in the table. With older versions of Informix, you'd run into problems with the size of the CREATE TABLE statement reaching the upper limit for the size of a statement (variously 32 kB and 64 kB, I think). I'm not sure whether there is still an upper limit on the size of a statement (256 kB is lurking in the back of my mind) or the size is now unlimited, or limited to 2 GB - 1. In practice, such a table is desparately boring and would not be any real use. Thanks and Regards, Gaurav Nirmala Kesavan <k7nirmala@yahoo. com> To Sent by: informix-list@iiug.org owner-informix-li cc st@iiug.org Subject max no of column with datatype LONG 10/07/2005 12:39 AM Dear members , Can anybody tell me how many columns can be created in a table ? and also how many columns can be created with data type LONG Regards, --------------------------------- Yahoo! for Good Click here to donate to the Hurricane Katrina relief effort. sending to informix-list [demime 1.01d removed an attachment of type image/gif which had a name of graycol.gif] [demime 1.01d removed an attachment of type image/gif which had a name of pic24941.gif] [demime 1.01d removed an attachment of type image/gif which had a name of ecblank.gif] sending to informix-list

Fnu Gaurav wrote: > http://groups.google.com/group/comp.databases.informix/browse_thread/thread/2 > e0279ed33ec18b4/350cf88479eb985c%23350cf88479eb985c?sa=X&oi=groupsr&start=0&n > um=3 Or try: http://tinyurl.com/bwoya Same place. The article dates back to 1999. > Above link has following reply by Jonathan Leffler: > > Well, the row size is limited to 32767 bytes. The minimum size of > column is CHAR(1). So, you could in principle have 32767 different > columns in the table. With older versions of Informix, you'd run into > problems with the size of the CREATE TABLE statement reaching the > upper limit for the size of a statement (variously 32 kB and 64 kB, I > think). I'm not sure whether there is still an upper limit on the > size of a statement (256 kB is lurking in the back of my mind) or > the size is now unlimited, or limited to 2 GB - 1. > In practice, such a table is desparately boring and would not be any > real use. Since then, I've done some research. The statement limit is still 64 KB. It is possible to create a table with 32767 columns in it; somewhere, I have the sequence of statements that does it. Since the column names have to be several characters long, you can't do it in one statement and stay within the 64 KB statement length limit. You also cannot successfully import this database -- IIRC, DB-Export manages it, but DB-Import doesn't have a clue how to split up an overlong SQL statement (and I'm not entirely sure it needs to, though several years ago I reported a bug or feature request pointing out there are many databases that DB-Import/DB-Export do not handle, and some of those databases are a lot more reasonable than one containing this maximal table). > Nirmala Kesavan <k7nirmala@yahoo.com> asked: > Can anybody tell me how many columns can be created in a table? > and also how many columns can be created with data type LONG. There are (at least) two answers to the 'LONG' question. 1. Zero: IDS does not have a LONG data type. 2. A couple of hundred. There is an upper limit on the number of special columns that you can have in a database, and I ran into it a week or two ago in some context, and the number was something like 230. There were indications that it is somewhat version specific, and generally lower in older versions. Physically, a BYTE or TEXT blob occupies 56 bytes in the row, so there is an upper upper limit of 32767/56 = 585 BYTE/TEXT columns that could be fitted into a 32KB row size. However, the limit in practice is lower because of the number of special columns. BLOB and CLOB types use slightly more space (72, 76 bytes per descriptor?), but you run out of slots for special columns before you run out of space in the row for the descriptors. IIRC, VARCHAR and LVARCHAR columns also count as special columns. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/