How to Create a Database Table with SQLScript

Let’s talk about the most important object type in a SQLScript database—the database tables.

The current reference documentation (http://s-prs.co/v533625) for the CREATE and ALTER TABLE statements alone consists of almost 50 pages with brief, keyword-like descriptions for all the options available for defining and altering database tables. In the following sections, we’ll focus on basic table definition and describe some interesting features from a developer perspective. Thus, we won’t cover the parameterization for administration, partitioning, storage behavior, etc.

Creating Database Tables

In the simplest variant, to create a database table, you only need to define the columns in the table definition of the CREATE TABLE statement, for example, in the following way:

CREATE TABLE status (id INT ,

           text NVARCHAR(30) );

All other table properties correspond to the default values of the CREATE TABLE statement. In the following sections, we describe how you can define the most important properties of database tables.

Column Definition

Columns are defined after the table name in parentheses. All column definitions are listed one after the other, separated by commas. Some example definitions are shown below.

<columnname>

   <datatype>

   [DEFAULT <defaultvalue>]

   [<constraint>]

A default value is used when a new record is inserted, but no value is specified for this column. In addition, you can also define constraints for each column, as shown below, such as the following constraints:

• NOT NULL: The column value must be defined; otherwise, a data record can’t be
• UNIQUE: In this column, every value (except NULL) may occur only once.
• PRIMARY KEY: The column is the sole primary key. As a result, this column automatically has the properties NOT NULL and UNIQUE.
  

CREATE TABLE status (id INT PRIMARY KEY,

           sort_nr INT NOT NULL UNIQUE,

                text NVARCHAR(30) );

If the two constraints UNIQUE or PRIMARY KEY refer to more than one column, you can also specify the respective constraints after the relevant column definitions, as shown in this listing.

CREATE TABLE test_unique (a INT,

                b INT,

                c INT,

           UNIQUE(a, b),

                UNIQUE(b, c));

In the example shown above, the combined values from columns A/B and B/C must be unique for each row. Note that multiple NULL values are permitted in this context. A composite primary key is similar to a UNIQUE constraint, and additionally, the NOT NULL constraint is valid for each column, as shown here.

CREATE TABLE test_composite_key (a INT,

   b INT,

   c INT,

   PRIMARY KEY(a, b));

Type of Table

In the context of an SAP HANA database, we basically differentiate between two types, ROW and COLUMN, for tables. The type of a database table is determined optionally during the process of its creation using the following statement:

CREATE [<type>] TABLE

The ROW value for a type means that the table contents will be stored row by row in the row store. This type is therefore particularly suitable if only a few data records need to be read but their full width is relevant.

In contrast, the table type COLUMN stores the data column by column in the column store. This type may seem absurd at first, but the advantage is that, for example, with more complex SQL queries, only the columns relevant for determining the result set will be processed first. Additional columns can be added later. SAP recommends using COLUMN tables whenever you need to process a large number of data records, of which you only need a relatively small number of columns.

When selecting the appropriate storage type, you should also take into account that read access to ROW and COLUMN tables are controlled by different engines. A join using tables of both types forces the engines to change, which means that the data must be copied once.

The following four types can be used to create temporary tables.

• GLOBAL TEMPORARY ROW
• GLOBAL TEMPORARY COLUMN
• LOCAL TEMPORARY ROW
• LOCAL TEMPORARY COLUMN

Default Value for the Table Type

Up to and including SAP HANA 2.0 Support Package Stack (SPS) 02, the default value for the type is ROW. With the release of SPS 03, the default value has been set to COLUMN. If you need to reverse this change, you can define the default value yourself in the configuration file indexserver.ini via the default_table_type parameter. This parameter was available before SPS 03 and won’t be overwritten during upgrades.

You can find more information on this topic in SAP Note 2551355.

Automatic Number Assignment

You can specify that a column should be automatically filled with sequential numbers, which is similar to using a sequence.

The addition GENERATED BY DEFAULT AS IDENTITY at the end of a column definition causes the assignment of a sequential number if no value for the column has been specified during the insertion process. Alternatively, you can use GENERATED ALWAYS AS IDENTITY to constantly force the generation of a sequential number, as shown here.

CREATE COLUMN TABLE test_identity (

a INT GENERATED BY DEFAULT AS IDENTITY,

b VARCHAR(10));

INSERT INTO test_identity (b) VALUES ('One');

INSERT INTO test_identity (b) VALUES ('Two');

INSERT INTO test_identity (a,b) VALUES (3, 'Three');

INSERT INTO test_identity (b) VALUES ('Four');

SELECT * FROM test_identity;

For the number assignment, you can also use other options such as a start value or an increment by which an increase should be carried out.

Copying a Table

If you want to create a table that should be defined exactly like an existing table, you can use the following statement:

CREATE TABLE <tablename>

   LIKE <originaltable>

        [WITH DATA];

The addition WITH DATA ensures that all data from the original table is copied into the new table. Especially for testing and error analysis, such table copies can be quite useful.

Creating a Table Based on an SQL Query

You can also create database tables with a SELECT query, as shown below. In this context, the columns of the field list are used for the column definition.

CREATE TABLE tmp_tasks AS (

         SELECT a.id,

                b.firstname,

                b.lastname,

                t.team_text

                FROM tasks AS a

                LEFT OUTER JOIN users AS b

                ON a.assignee = b.id

                LEFT OUTER JOIN team_text AS t

                ON b.team = t.id

                )

                WITH DATA;

As with CREATE TABLE ... LIKE ..., you can use the WITH DATA addition as well. This addition inserts the query data directly into the new database table, which can be quite helpful for testing and error analysis. For example, if you want to save the contents of a table for later comparison, this variant of the CREATE TABLE statement enables you to store the result as a new table.

Changing Database Tables

When database tables are changed, the individual properties of the existing definition are changed. For this reason, the ALTER statement doesn’t contain the entire table definition, only the properties that are actually to be changed. The following listing contains some examples of changing the most important table properties.

CREATE ROW TABLE demo_table(

col1 INT,

col2 INT

);

--Adding a column

ALTER TABLE demo_table ADD (col3 VARCHAR(20));

--Change column properties, e.g., set default value

ALTER TABLE demo_table ALTER (col1 INT DEFAULT 42);

--Add the primary key definition

ALTER TABLE demo_table

           ADD CONSTRAINT pk PRIMARY KEY (col1, col2);

--Changing the type

ALTER TABLE demo_table COLUMN;

In the database catalog, right-click on the relevant table and select Open Definition from the context menu to view the current table definition. Now, you can reproduce the changes from the listing step by step. The figure below shows the state of the table definition once all changes have been made.

Deleting Database Tables

When deleting database tables, the details of the definition aren’t necessary. The statement DROP TABLE <tablename> causes the permanent deletion of a table including its entire contents.

Proceed with Caution When Deleting: The DROP TABLE statement enables you to delete entire tables including their contents. No security question will ask, “Are you sure you want to delete the table and its contents?” to confirm the action.

Thus, this statement is extremely dangerous, especially in combination with dynamic SQL. You should only execute this statement for production tables if you’re 100% sure deletion won’t cause a major problem.

Before deleting a table, cautious people will use CREATE TABLE ... LIKE ... WITH DATA to create a backup copy of the table, including its data.

The RESTRICT addition ensures that a table is deleted only if no dependencies with other objects exist. Alternatively, the CASCADE addition allows you to determine that dependent objects should be deleted as well.

Editor’s note: This post has been adapted from a section of the book SQLScript for SAP HANA by Jörg Brandeis.