|
Category: Definition: Aggregate Functions: Aggregate Functions
Table, Tables
A table is a way to present, order and combine of text or all kinds of data.
The contents of a table is organised in rows and columns, which are graphically aligned.
Rows and columns meet at a right angle.
"Table fields" are located at the crossing points between rows and columns.
The first column of a table is called "head column", the first row is called "header row".
A table is the central element in a
relational database,
so also in
ASE servers.
In ASE server a table is also called an
object.
The tables of a relational database are combined with each other by the data values in their columns.
These columns might contain character strings, illegible for the observer,
which are responsible for associating data in other tables. Thus, the contents of certain tables might only be sensible and understandable when seen in context.
Table Name Conventions
Before creating, the structure of the table should be considered.
The name of the table and the definition of its columns are part of these considerations.
When creating a table, every column must be assigned a
datatye.
The name of the table must not be longer than 30 characters and must start either
with a character or an underscore (_). All following characters of the table name can be a combination of characters, numbers and the symbols $, #, @, £ and ¥.
Restrictions for table names
The name of a table must be restricted to 30 characters if a single-byte
"charcter set"
is used. An example would be the English character set. Furthermore, the table name must not be a
T-SQL reserved word.
Maximum number of columns in a table
Depending on their
locking scheme
and the datatpyes used, tables can be defined with a differing number of columns.
With
APL tables
and
DOL tables
the maximum number of columns with fixed length datatypes is set to 1024. The maximum number of columns with variable length datatypes is set to 254 in APL tables and 1024 in DOL tables.
The maximum size of columns in a table
The size of a column depends on whether the columns of a table are defined with fixed-length
or variable-length datatypes. Additionally, the ;logical page size" of the database influences the
size of a column. For example, in a database with a logical page size of 2K,
the maximum size of a column in APL tables can be 1962 bytes, less the overhead (2K) for row formatting.
When attempting to create a table with fixed-length columns, which are larger than the maximum allowed,
an error message is returned.
Overview of maximum sizes for rows and columns of a table
| Locking scheme
| Page size
| Maximum row length
| Maximum column length
|
|
2K (2048 bytes) |
1962 |
1960 bytes |
| 4K (4096 bytes) |
4010 |
4008 bytes |
| APL tables |
8K (8192 bytes) |
8106 |
8104 bytes |
| 16K (16384 bytes) |
16298 |
16296 bytes |
| 2K (2048 bytes) |
1964 |
1958 bytes |
| 4K (4096 bytes) |
4012 |
4006 bytes |
| DOL tables |
8K (8192 bytes) |
8108 |
8102 bytes |
| 16K (16384 bytes) |
16300 |
16294 bytes* |
| 16K (16384 bytes) |
16300** |
8191-6-2 = 8183 bytes*** |
* if the table does not contain any columns with variable-length datatypes.
** subject to a max start offset of varlen = 8191.
*** if the table contains at least one column with a variable-length datatype.
The size includes six
bytes
for the row overhead and two bytes for the field length.
|
See also:
Datatypes,
create table,
alter table,
drop table,
sp_chgattribute,
sp_checksource,
sp_depends,
sp_estspace,
sp_help,
sp_helpartition,
sp_hidetext,
sp_placeobject,
sp_recompile,
sp_rename,
sp_spaceused,
create chema authorization
and
NULL Properties.
|
