Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » UltraLite - Database Management and Reference » Using UltraLite Databases » Working with UltraLite databases

 

Working with UltraLite tables and columns

Tables are used to store data and define the relationships for data in them. Tables consist of rows and columns. Each column carries a particular kind of information, such as a phone number or a name, while each row specifies a particular entry.

When you first create an UltraLite database, the only tables you will see are the system tables. System tables hold the UltraLite schema. You can hide or show these tables from Sybase Central as needed.

You can then add new tables as required by your application. You can also browse data in those tables, and copy and paste data among existing tables in the source database or even among other open destination databases.

Row packing and table definitions

UltraLite works with rows in two formats:

  • Unpacked rows   are the uncompressed format. Each row must be unpacked before individual column values can be read or written.

  • Packed rows   are the compressed representation of the unpacked row, where each of the column values is compressed so that the entire row takes up as little memory as possible. The size of a packed row depends entirely on the values in each column: for example, two rows can belong to the same table, but can differ significantly in their packed size. Note also that LONG BINARY and LONG VARCHAR columns are stored separate from the packed row.

UltraLite has a limitation that a packed row must fit on a database page. Since LONG BINARY and LONG VARCHAR columns are not stored with the packed row, they can exceed the page size.

It is important to understand that table definitions describe the row before the UltraLite runtime packs the data. Because the size of a packed row depends on the values in each column, you cannot readily pre-determine from the table definition whether the packed row requirement is satisfied. For this reason, UltraLite allows you to define a table where an unpacked row would not fit on a page. To know if a row fits on a page, you must try inserting or updating the row itself; if a row does not fit, UltraLite detects and reports this error.

Note

You cannot declare tables to be any large size you require. UltraLite maintains a declared table row size limit of 64 KB. If you try to define a table where an unpacked row can exceed this maximum, UltraLite generates a SQL error code of SQLE_MAX_ROW_SIZE_EXCEEDED (-1132).

See also

Creating UltraLite tables
Using allsync and nosync suffixes
Adding a column to an UltraLite table
Altering UltraLite column definitions
Deleting UltraLite tables
Browsing the information in UltraLite tables
Copying and pasting data to or from UltraLite databases
Viewing entity-relationship diagrams from the UltraLite plug-in