Working with tables
120
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”.
Specifying data types
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.
Types of tables
Adaptive Server IQ recognizes four types of tables:
•
Base tables
•
Local temporary tables
•
Global temporary tables
•
Join virtual tables
Base tables are
permanent
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.
Creating temporary
tables
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
Summary of Contents for Adaptive Server IQ 12.4.2
Page 1: ...Administration and Performance Guide Adaptive Server IQ 12 4 2 ...
Page 16: ...xvi ...
Page 20: ...Related documents xx ...
Page 40: ...Compatibility with earlier versions 20 ...
Page 118: ...Troubleshooting startup shutdown and connections 98 ...
Page 248: ...Importing data by replication 228 ...
Page 306: ...Integrity rules in the system tables 286 ...
Page 334: ...Cursors in transactions 314 ...
Page 396: ...Users and permissions in the system tables 376 ...
Page 438: ...Determining your data backup and recovery strategy 418 ...
Page 484: ...Network performance 464 ...
Page 500: ...System utilities to monitor CPU use 480 ...
Page 514: ...Characteristics of Open Client and jConnect connections 494 ...
Page 536: ...Index 516 ...
