Many languages, such as English, Russian, Chinese, and Greek, have different characters in their alphabet. If English is the default language on your computer, the English character set contains only 255 different characters and can’t be used for Russian, Chinese, or Greek.
The NATIONAL CHARACTER set uses the code in two bytes of data for each character of the alphabet. This Unicode can encode up to about 4.3 billion characters (using UTF-32 encoding). The Unicode Consortium maintains the unicode standard. See www.unicode.org.

NATIONAL CHARACTER is the same as NATIONAL CHAR and NCHAR.

 

Example:    COL1 NATIONAL CHARACTER(20)

COL1 is a maximum of 20 double-byte characters in length.

This is used for a character string that has a long variable length. When you have long data that will vary greatly in length and it is important to save disk space, the LONG VARCHAR definition is used. If the maximum column length is large, and most of the time the column will be blank, then a large amount of storage can be saved.

The (length) represents an integer for the maximum length of this column definition and must be 1 or greater.
Some DBMS systems limit this to 32,767 characters and use VARCHAR for up to 255 characters.

Alphanumeric data can be stored in this definition.

If the length of the data entered into this column is shorter than the column definition, then the data is stored as is. For example, if the length is set to 2000 characters and only 200 characters are entered, then the length of the data is recorded with the 200 characters of data. 

 

Example:    COL1 LONG VARCHAR(2000)

COL1 is a maximum of 2,000 characters in length.
> Long variable columns should be defined only at the end of a table.
> DB2 uses LONG VARCHAR. This option should be used only when needed and has some restrictions.
It can’t be sorted by ORDER BY, GROUP BY, DISTINCT, or UNION without the ALL option.
It can’t be in a WHERE clause except with the LIKE clause.

This is used for a character string that has a variable length. When you have data that will vary greatly in length and it is important to save disk space, the VARCHAR definition is used. If the maximum column length is small, then VARCHAR is probably not a good idea. If the maximum column length is large and most of the time the column will be blank, giving an average column length that is small, then a large amount of storage can be saved.

The (length) represents an integer for the maximum length of this column definition and must be 1 or greater.
Some DBMS systems limit this to 255 characters and use LONG VARCHAR for up to 32,767 characters.

Alphanumeric data can be stored in this definition.

If the length of the data entered into this column is shorter than the column definition, then the data is stored as is. For example, if the length is set to 200 characters and only 20 characters are entered, then the length of the data is recorded with the 20 characters of data.

VARCHAR is the same as CHAR VARYING and CHARACTER VARYING. We use VARCHAR in this course.

 

Example:    COL1 VARCHAR(200)

COL1 is a maximum of 200 characters in length.
> Variable columns should be defined only at the end of a table.
> MySQL and SQL Server use VARCHAR.
> Oracle uses VARCHAR and VARCHAR2.

This is used for a character string that always has the same length.

The (length) represents an integer for the maximum length of this column definition and must be 1 or greater.

Alphanumeric data can be stored in this definition.

If the length of the data entered into this column is shorter than the column definition, then the data is padded with spaces to the length specified by (length). For example, if the length is set to 20 characters and only 8 characters are entered, then 12 spaces are added to the end. If the data is too long, then it will be truncated.

CHAR is the same as CHARACTER. We use CHAR in this course.

 

Example:    COL1 CHAR(30)

COL1 is 30 characters in length.

Character String Data is coded by the RDBMS vendors as follows:

Character string data is represented by the following:> A sequence of zero or more characters.

> The length can be fixed or varying.

> In an SQL statement, it is a string of characters surrounded by single quotes.

> It is case sensitive, with upper- and lower-case letters, as well as numbers, spaces, and special characters.

 

> The length of a string is an integer, including zero. An empty string of no characters is represented by two single quotes with no space between the quotes (”). This is a zero-length string.
> If you wish to place a single quote in a data value, you do this with two single quotes, so ‘I can”t go’ means, “I can’t go.” A double-quote character (”) doesn’t need this special treatment.
> SQL can handle and sort fixed-length strings faster than variable-length strings.
> Keep column data size as small as possible, because too much volume will waste time and storage.
> SQL:1999 introduced CLOB and NCLOB, but most RDBMS systems already had similar data types.
> Oracle treats empty strings as null.
> In MySQL, if the ANSI_QUOTES option is turned on, string literals can only be quoted with single quotes and an identifier is surrounded by double quotes.

For a RDBMS system, such as DB2, the values and physical lengths of the data are as follows:

       TYPE                  VALUES               PHYSICAL LENGTH
Character columns:
 CHAR(length)            Any Character        1 to 254 + 1
 VARCHAR(max)            Any Character        1 to 254 + 1
 LONG VARCHAR(max)       Any Character        1 to 32,765

Numeric columns:
 DECIMAL(total,fract)    30 (15) Digits       total/2 + 1
 INTEGER                 +- 2,147,483,647     5
 SMALL INTEGER           +- 32,767            3
 FLOAT(length)           -5.4E-79 to +7.2E+75 5 if length from 1 to 21
                                              9 if length from 22 to 53

Date/Time columns:
 DATE                    00010101 to 99991231 5
 TIME                    00:00 to 23:59       4
 TIMESTAMP               Any Date-Time        11

Graphic columns:
 GRAPHIC(length)         Any DBCS graphic     2*length + 1 (2 to 254 + 1)
 VARGRAPHIC(length)      Any DBCS graphic     2*length + 1 (2 to 254 + 3)
 LONG VARGRAPHIC(length) Any DBCS graphic     2*length + 3 to data page size

Check with your RDBMS documentation to confirm the values and physical length of data used by your system.

While the implementation of the Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE) has evolved into a fairly uniform implementation by the vendors, the implementation of data types has been in more disarray. Data-type implementation normally evolves as follows:

> An RDBMS vendor adds a new data type that is of value to a group of users.

> Other RDBMS vendors add the same or a similar data type with their own variation to differentiate their product.

> Several years pass and the data type grows in popularity with most vendors implementing a similar version.

> The standards committee gets involved to try to standardize the data type and eliminate variations.

> The RDBMS vendors slowly support the new standardized data type, but they must continue to support the users who have implemented their previous variations.

> Over several major releases from the vendor, users will migrate to the new standard data type and the previously implemented variation can be slowly phased out.

A good example of this process is DB2’s implementation of DATE, TIME, and TIMESTAMP.

 

 

Each column in a table has a single data type.
The data type determines the allowable values, and what operations can be performed on the data.
Arithmetic operations can be performed on numeric data types.
Concatenation can be performed on string data types.

The four basic data type groups are:

Character: CHAR(length) .... VARCHAR(length) .... LONG VARCHAR(length)
Graphic:   GRAPHIC(length) . VARGRAPHIC(length) . LONG VARGRAPHIC(length)
Numeric:   SMALLINT ........ INTEGER ............ DECIMAL(precision,scale) . FLOAT(precision)
Date/Time: DATE ............ TIME ............... TIMESTAMP ................ INTERVAL

> Character and graphic data can be grouped into a larger category called string data.
> Use CREATE TABLE or ALTER TABLE to define or change a column’s data type.
> Data type affects the column’s sort order. You can sort SELECTed data by using ORDER BY.
Numeric 11, 10, 1, 2, 3 is sorted to 1, 2, 3, 10, 11.
Characters ‘11′, ‘10′, ‘1′, ‘2′, ‘3′ is sorted to ‘1′, ‘10′, ‘11′, ‘2′, ‘3′.
> Some data types such as LONG VARCHAR, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC are rarely used.
> VARCHAR and LONG VARCHAR should only be used at the end of a row as explained later.
> SQL-89 defined a minimum of data set types, but it omitted some of the most useful and popular types, such as dates, times, and variable-length characters.
> SQL-92 included these useful data types, but didn’t address newer types, such as graphics and multimedia.
> SQL:2003 dropped SQL-92’s bit string data type such as BIT and BIT VARYING.
> Other data types may be implemented by the RDBMS vendors.