Home » SQL & PL/SQL » SQL & PL/SQL » max no. of columns
max no. of columns [message #207544] Wed, 06 December 2006 00:12 Go to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
what maximum number of columns can be create in a table? It is a interview question. Plz reply me. Thanks
Amit Vashishtha
Re: max no. of columns [message #207546 is a reply to message #207544] Wed, 06 December 2006 00:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1000.
If you want more than that,look into nested columns.
And please search before posting.
Most of these questions are already answered.
Re: max no. of columns [message #207563 is a reply to message #207544] Wed, 06 December 2006 01:03 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
FROM INFOCENTER.SYBASE.COM SITE I FOUND THIS MATTER.
Number of columns and size
The maximum number of columns you can create in a table is:

1024 for fixed-length columns in both all-pages-locked (APL) and data-only- locked (DOL) tables

254 for variable-length columns in an APL table

1024 for variable-length columns in an DOL table

The maximum size of a column depends on:

Whether the table includes any variable- or fixed-length columns.

The logical page size of the database. For example, in a database with 2K logical pages, the maximum size of a column in an APL table can be as large as a single row, about 1962 bytes, less the row format overheads. Similarly, for a 4K page, the maximum size of a column in a APL table can be as large as 4010 bytes, less the row format overheads. See Table 0-1 for more information.

If you attempt to create a table with a fixed-length column that is greater than the limits of the logical page size, create table issues an error message.
Re: max no. of columns [message #207568 is a reply to message #207563] Wed, 06 December 2006 01:21 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You showed us what SYBASE says about it.

This is what Oracle says:
Quote:
ORA-01792: maximum number of columns in a table or view is 1000

Cause: An attempt was made to create a table or view with more than 1000 columns, or to add more columns to a table or view which pushes it over the maximum allowable limit of 1000. Note that unused columns in the table are counted toward the 1000 column limit.

Action: If the error is a result of a CREATE command, then reduce the number of columns in the command and resubmit. If the error is a result of an ALTER TABLE command, then there are two options: 1) If the table contained unused columns, remove them by executing ALTER TABLE DROP UNUSED COLUMNS before adding new columns; 2) Reduce the number of columns in the command and resubmit.
Previous Topic: top 10 rows
Next Topic: Convert TABLE type object into CLOB
Goto Forum:
  


Current Time: Thu Dec 08 04:02:56 CST 2016

Total time taken to generate the page: 0.09241 seconds