Databases and Tables

A database stores information (data) in a set of database objects, such as tables, that relate to each other. A table is a collection of rows that have associated columns containing individual data items.

When you create databases and tables, you are deciding the organization of your data. This process is called data definition.

Columns and datatypes define the type of data included in tables. Indexes describe how data is organized in tables. They are not considered database objects by SAP ASE and are not listed in sysobjects.

• Databases
  A database is a collection of related tables and other database objects—views, indexes, and so on.
• Create a User Database
  You can create a new database if a system administrator has granted you permission to use create database. You must be using the master database when you create a new database.
• Choose a Database
  The use command lets you access an existing database if you are a known user:
• Permissions Within Databases
  Permissions or privileges you are granted determine the actions you can perform on databases and database objects.
• Initialize Databases Asynchronously
  The async_init parameter for the alter database and create database commands lets you asynchronously initialize a database while it is being used.
• Drop Databases
  Use the drop database command to remove a database. drop database deletes the database and all of its contents from SAP ASE, frees the storage space that had been allocated for it, and deletes references to it from the master database.
• Change the Database Size
  If a database has filled its allocated storage space, you cannot add new data or updates to it. Existing data is always preserved. If the space allocated for a database proves to be too small, the database owner can use the alter database command to increase it.
• Enforce Data Integrity in Databases
  Data integrity refers to the correctness and completeness of data within a database. To enforce data integrity, you can constrain or restrict the data values that users can insert, delete, or update in the database.
• quiesce database Command
  The quiesce database command suspends and resumes updates to a specified list of databases.
• Tables
  When you create a table, you name its columns and supply a datatype for each column. You can also specify whether a particular column can hold null values, or specify integrity constraints for columns in the table.
• Designing and Creating a Table
  Use the create table statement to create a practice table. If you do not have create table permission, see a system administrator or the owner of the database in which you are working.
• Create Tables in Different Databases
  You can create a table in a database other than the current one by qualifying the table name with the name of the other database.
• Create New Tables from Query Results: select into
  The select into command lets you create a new table based on the columns specified in the select statement’s select list and the rows specified in the where clause.
• Temporary Tables Usage
  Temporary tables are created in the tempdb database. To create a temporary table, you must have create table permission in tempdb; this permission defaults to the database owner.
• Deferred Table Creation
  The with deferred_allocation parameter for the create table command lets you defer page allocation for a table. The use of deferred tables can improve performance for applications that create numerous tables, but use only a small number of them. Tables are called “deferred” until SAP ASE allocates their pages.
• IDENTITY Columns Usage
  An IDENTITY column contains a value for each row, generated automatically by SAP ASE, that uniquely identifies the row within the table.
• Allow Null Values in a Column
  If you omit null or not null in the create table statement, SAP ASE uses the null mode defined for the database (by default, NOT NULL). Use sp_dboption to set the allow nulls by default option to true.
• Alter Existing Tables
  Use the alter table command to change the structure of an existing table.
• Drop Tables
  Use drop table to remove specified tables from the database, together with their contents and all indexes and privileges associated with them. Rules or defaults that are bound to the table are no longer bound, but are otherwise not affected.
• Manage Identity Gaps in Tables
  The IDENTITY column contains a unique ID number, generated by SAP ASE, for each row in a table.
• Define Integrity Constraints for Tables
  To maintain data integrity in a database, you can either define rules, defaults, indexes, and triggers; or, you can define create table integrity constraints.
• Computed Columns
  Computed columns, computed column indexes, and function-based indexes provide convenient data manipulation and faster data access.
• Retrieve Information About Databases and Tables
  SAP ASE includes several procedures and functions you can use to get information about databases, tables, and other database objects.