Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 49 50 51 52 53 54 < 55 > 56 57 58 59 60 61 .. 241 >> Next


This form . . . Inserts the current . . . Using this data type . . .
DEFAULT DATEa date DATE, INTEGER
DEFAULT TIMEb time FLOAT
DEFAULT USER user name CHAR(n), VARCHAR(n) where n can be length of longest username

a. Flagged as non-ANSI when the SQL flagger is enabled.

b. Flagged as non-ANSI when the SQL flagger is enabled.

5-74

Teradata RDBMS for UNIX SQL Reference
Data Definition

Default Control Phrase

WITH DEFAULT Phrase

Table 5-14

System Values for WITH DEFAULT Phrase

For example, if the DeptNo column is defined as:

DeptNo SMALLINT DEFAULT 100 FORMAT'999'

BETWEEN 100 AND 900,

and the value for the DeptNo field is not provided in an INSERT statement, then the value 100 is inserted automatically.

If a column does not have a default value specified by the DEFAULT or WITH DEFAULT phrase, the default for the column is null.

No error or warning messages are given if DEFAULT NULL is specified with the NOT NULL phrase in CREATE TABLE or ALTER TABLE ADD statements.

You can assign a value, such as a blank, with the DEFAULT phrase as follows:

CHAR(I) DEFAULT ' ' ...

The WITH DEFAULT phrase specifies that a system default value is to be inserted in the field when a value is not specified in an INSERT statement. This phrase is flagged as non-ANSI when the SQL flagger is enabled.

The value of a system default is determined by the data type defined for the column. The data types and associated system default values are listed in Table 5-14.

Data Type System Default
BYTE[(n)] 0 if n omitted, or n binary zeros
BYTEINT 0
CHAR[(n)] blank if n omitted, or n blanks
DATE current date
DECIMAL and NUMERIC 0
FLOAT 0
REAL 0
DOUBLE PRECISION 0
INTEGER 0
LONG VARCHAR ’ ’ (null string)
SMALLINT 0
VARBYTE (n) ’ ’ (null string)
VARCHAR(n) ’ ’ (null string)

Teradata RDBMS for UNIX SQL Reference

5-75
Data Definition

Default Control Phrase

Example 1

Example 2

Data Type System Default
GRAPHIC[(n)] graphic pad character if n is
omitted; otherwise, n graphic
pad characters.
VARGRAPHIC(n) ’ ’ (null string)
LONG ’ ’ (null string)
VARGRAPHIC

When a CREATE TABLE statement is processed, any WITH DEFAULT phrase is converted to a DEFAULT phrase in which the appropriate system value becomes the default constantvalue.

If the EdLev column is defined as:

EdLev BYTEINT FORMAT 'Z9' NOT NULL WITH DEFAULT,

then the column definition for EdLev is stored as:

EdLev BYTEINT NOT NULL DEFAULT 0 FORMAT 'Z9',

The following INSERT statement adds a row containing a 0 in the 5 EdLev field:

INSERT INTO Employee

(Name, EmpNo, DeptNo, DOB, Sex, EdLev)

VALUES ('Newhire A', 10025, '49/10/17', 'M',) ;

As is the case for explicit default values, WITH DEFAULT may generate a value that violates some CHECK constraint on the table. The conflict will not be recognized until an INSERT or UPDATE provides a NULL value for the column.

Caution: Do not use a default value that violates defined constraints.

5-76

Teradata RDBMS for UNIX SQL Reference
Data Definition

COMPRESS Phrase

Introduction

Example: const CHAR Data Type

COMPRESS Phrase

The COMPRESS phrase is flagged as non-ANSI when the SQL flagger is enabled.

The COMPRESS phrase allows values in one or more columns of a table to be compressed to zero space.

The syntax for the COMPRESS phrase is:

------COMPRESS —

— NULL —

— const —

FF07A066

where const is the value to be compressed.

WHEN .. . THEN . . .
the NULL phrase is used nulls are compressed. Data for that value would not take much space hence using COMPRESS would not necessarily save much space.
a const is specified the value are compressed.
COMPRESS is used alone nulls are compressed to zero space.

If const is specified for a CHAR data type without the UPPERCASE option, a data value must match the case of ’constantvalue’ in order to be compressed.

For example, if a column named “Doc_Type” is defined as follows:

Doc_Type CHAR(6) COMPRESS 'Manual'

then each value must be entered as ’Manual’ in order to be compressed. A value in a different case, such as ’MANUAL’, ’manual’, or ’mANUAL’, is not compressed.

COMPRESS can be used with any fixed-length character, byte, or numeric data type. Fixed-length character or byte columns cannot be compressed if they are longer than 255 bytes.

As many columns as is practical can be compressed for any table.

Teradata RDBMS for UNIX SQL Reference

5-77
Data Definition

COMPRESS Phrase

Only one COMPRESS phrase can be defined per column.

WHEN the percentage of compressible values is . . . AND the column is . . . THEN . . .
high large COMPRESS can be valuable, especially if the table will occupy fewer data blocks due to the shortened rows. This can realize a significant savings in resources as well as space, because accessing a data block consumes a fixed amount of CPU and I/O resources.
low • small • frequently accessed • has a higher column id than other compressed columns the increase in access time may offset the savings in space
Previous << 1 .. 49 50 51 52 53 54 < 55 > 56 57 58 59 60 61 .. 241 >> Next