Sybase 12.4.2 manual Types of tables, 120

Working with tables

Specifying data types

Types of tables

Base tables are permanent

Creating temporary tables

By internally executing the COMMIT statement before creating the table, Adaptive Server IQ makes permanent all previous changes to the database. There is also a COMMIT after the table is created.

For a full description of the CREATE TABLE statement, see “CREATE TABLE statement” in the Adaptive Server IQ Reference Manual. For information about building constraints into table definitions using CREATE TABLE, see Chapter 7, “Ensuring Data Integrity”.

When you create a table, you specify the type of data that each column holds.

You can also define customized data types for your database. In the Adaptive Server IQ Reference Manual, see “SQL Data Types” for a list of supported data types, or see the CREATE DOMAIN statement for details on how to create a customized data type.

Adaptive Server IQ recognizes four types of tables:

•Base tables

•Local temporary tables

•Global temporary tables

•Join virtual tables

Base tables are sometimes called main or permanent tables, because they are stored in the main IQ Store, and are a permanent part of the database, until you drop them explicitly. Base tables and the data in them are accessible to all users who have the appropriate permissions. The CREATE TABLE statement shown in the previous example creates a base table.

There are two types of temporary tables, global and local.

You create a global temporary table, using the GLOBAL TEMPORARY option of CREATE TABLE, or by specifying in the Sybase Central table editor that this is a temporary table. When you create a global temporary table, it exists in the database until it is explicitly removed by a DROP TABLE statement.

A database contains only one definition of a global temporary table, just as it does for a base table. However, each user has a separate instance of the data in a global temporary table. Those rows are visible only to the connection that inserts them. They are deleted when the connection ends.

To select into a temporary table, use syntax like the following:

SELECT * INTO #TableTemp FROM lineitem

120