Tables

Create, delete, and display tables (including global temporary tables and proxy tables), and manage their properties, in SAP Sybase IQ.

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 a database is first created, the only tables in the database are the system tables, which hold the database schema. You can create new tables to hold your actual data.

• Creating a Table with No Partitions in the IQ Main Store
  A base table holds persistent data. (The table and its data continue to exist until you explicitly delete the data or drop the table.)
• Creating a Table with No Partitions in the IQ (Catalog) System Store
  A base table holds persistent data. (The table and its data continue to exist until you explicitly delete the data or drop the table.)
• Creating a Table with a Hash Partition
  A table with a hash partition can only reside in the IQ main store.
• Creating a Table with a Range Partition
  A table with a range partition can only reside in the IQ main store.
• Creating a Table with a Hash-Range Partition
  A table with a hash-range partition can only reside in the IQ main store.
• Creating a Global Temporary Table
  Add a global temporary table to the database. A global temporary table definition is kept in the database permanently. (The data disappears when you close the connection.)
• Creating a Proxy Table
  A proxy table is a locally named alias, displayed as a table, that points to a remote object such as an entire table, a view, a materialized view, or a set of remote table columns. Before you can map remote objects to a local proxy table, you must define the remote server where the remote object is located.
• Viewing Table Data in the Execute SQL Window
  Execute a SELECT * FROM query and display contents of the table, global temporary table or proxy table in the query results pane of the Execute SQL window.
• Deleting a Table
  Delete a table, global temporary table or proxy table from the database.
• Generating Table DDL Commands
  Display the data description language SQL code for adding a table, global temporary table or proxy table to a dbspace. The DDL can be a useful reference and training tool.
• Moving a Table to Another Dbspace
  You can move the table from the current dbspace to another dbspace. This feature is available only for tables in the IQ main store.
• Validating a System Store Table
  Check the validity of a system store (IQ catalog store) table. This feature is available only for tables in the system store.
• Setting the Primary Key
  Set the primary key to prohibit two or more rows in the table from having identical values for a column or set of columns. You can set the primary key on base tables and global temporary tables only.
• Setting a Clustered Index
  Set a clustered index on a system store (IQ catalog store) table. Using a clustered index increases the chance that two rows from adjacent index entries will appear on the same page in the database. This strategy can lead to performance benefits by reducing the number of times a table page needs to be read into the buffer pool.
• Merging Table Data from RLV Store with IQ Main Store
  Read-write data from a table enabled with row-level versioning (RLV) is housed in the RLV store, and periodically merged automatically with the table's data in the IQ main store. However, you can explicitly merge this RLV-enabled table data from the RLV store with the IQ main store data.
• Calculating the Number of Rows in a Table
  Calculate an accurate row count for a base or proxy table.
• Moving Table Objects to Another Dbspace
  You can move SAP Sybase IQ table objects—including indexes, columns, and partitions—from the current dbspace to another dbspace. This feature is available only for tables in the IQ main store.
• Enabling or Disabling Row-Level Versioning in a Table
  You can set a base table to use row-level versioning (RLV) in order to track row changes for read-write transactions.
• Viewing or Modifying Base Table Properties
  Display or change the properties of the selected IQ store (main store) or IQ system store (catalog store) base table.
• Viewing or Modifying Global Temporary Table Properties
  Display or change the properties of the selected global temporary table.
• Viewing and Modifying Proxy Table Properties
  Display the properties of the selected proxy table. Only the Comment property is editable.
• Table Privilege Summary
  A list of the system privileges and object permissions required to complete the various table tasks.
• Table Columns
  SAP Sybase IQ stores data in columns. Add, modify, and delete table columns.
• Table Permissions
  Grant or revoke table permissions to users and roles.
• Table Constraints
  Constraints help to ensure that the data entered in the table is correct. Constraints also provide information to SAP Sybase IQ that boosts performance.
• Table Indexes
  You can add multiple indexes to any column in a table to better support the queries you plan to run.
• Table Triggers
  A trigger is a special form of stored procedure that executes automatically when a statement that modifies data in a table is executed. Trigger management includes creating, modifying and deleting triggers on a database object.
• Table Partitions
  Dividing large tables into more manageable storage objects, called partitions, can improve shorten backup and restore times, provide a finer level of granularity for data validation, and support tiered storage.