INTEGER data type

Data of the INTEGER type has no fractional part, and its precision depends on the specific SQL implementation. As the database developer, you can't specify the precision.

The precision of a number is the maximum number of significant digits the number can have.

SMALLINT data type

The SMALLINT data type is also for integers, but the precision of a SMALLINT in a specific implementation can't be any larger than the precision of an INTEGER on the same implementation. In many implementations, SMALLINT and INTEGER are the same.

If you're defining a database table column to hold integer data and you know that the range of values in the column won’t exceed the precision of SMALLINT data on your implementation, assign the column the SMALLINT type rather than the INTEGER type. This assignment may enable your DBMS to conserve storage space.

BIGINT data type

The BIGINT data type is defined as a type whose precision is at least as great as that of the INTEGER type (it may be greater). The exact precision of a BIGINT data type depends on the SQL implementation used.

NUMERIC data type

NUMERIC data can have a fractional component in addition to its integer component. You can specify both the precision and the scale of NUMERIC data. (Precision, remember, is the maximum number of significant digits possible.)

The scale of a number is the number of digits in its fractional part. The scale of a number can't be negative or larger than that number’s precision.

If you specify the NUMERIC data type, your SQL implementation gives you exactly the precision and scale that you request. You may specify NUMERIC and get a default precision and scale, or NUMERIC (p) and get your specified precision and the default scale, or NUMERIC (p,s) and get both your specified precision and your specified scale. The parameters p and s are placeholders that would be replaced by actual values in a data declaration.

Say, for example, that the NUMERIC data type's default precision for your SQL implementation is 12 and the default scale is 6. If you specify a database column as having a NUMERIC data type, the column can hold numbers up to 999,999.999999. If, on the other hand, you specify a data type of NUMERIC (10) for a column, that column can hold only numbers with a maximum value of 9,999.999999. The parameter (10) specifies the maximum number of digits possible in the number. If you specify a data type of NUMERIC (10,2) for a column, that column can hold numbers with a maximum value of 99,999,999.99. In this case, you may still have ten total digits, but only two of those digits can fall to the right of the decimal point.

NUMERIC data is used for values such as 595.72. That value has a precision of 5 (the total number of digits) and a scale of 2 (the number of digits to the right of the decimal point). A data type of NUMERIC (5,2) is appropriate for such numbers.

DECIMAL data type

The DECIMAL data type is similar to NUMERIC. This data type can have a fractional component, and you can specify its precision and scale. The difference is that your implementation may specify a precision greater than what you specify — if so, the implementation uses the greater precision. If you do not specify precision or scale, the implementation uses default values, as it does with the NUMERIC type.

An item that you specify as NUMERIC (5,2) can never contain a number with an absolute value greater than 999.99. An item that you specify as DECIMAL (5,2) can always hold values up to 999.99, but if your SQL implementation permits larger values, then the DBMS won't reject values larger than 999.99.

Use the NUMERIC or DECIMAL type if your data has fractional positions, and use the INTEGER, SMALLINT, or BIGINT type if your data always consists of whole numbers. Use the NUMERIC type rather than the DECIMAL type if you want to maximize portability, because a value that you define as NUMERIC (5,2), for example, holds the same range of values on all systems.

DECFLOAT data type

The DECFLOAT data type is new in SQL: 2016. Unlike the REAL and DOUBLE PRECISION data types, which are approximate numeric types and only provide binary approximations of decimal data, DECFLOAT combines the accuracy of the exact numeric DECIMAL data type with the performance advantages of the FLOAT data type. This is important for business applications, where approximations are not acceptable.

REAL data type

The REAL data type gives you a single-precision, floating-point number — the precision of which depends on the SQL implementation. In general, the hardware you use determines precision. A 64-bit machine, for example, gives you more precision than does a 32-bit machine.

A floating-point number is a number that contains a decimal point. The decimal point can “float” to different locations in the number, depending on the number's value. Examples include 3.1, 3.14, and 3.14159 — and yes, all three can be used as values for Π — each with a different precision.

DOUBLE PRECISION data type

The DOUBLE PRECISION data type gives you a double-precision floating-point number, the precision of which again depends on the implementation. Surprisingly, the meaning of the word DOUBLE also depends on the implementation. Double-precision arithmetic is primarily employed by scientific users. Different scientific disciplines require different levels of precision. Some SQL implementations cater to one category of users, and other implementations cater to other categories of users.

In some systems, the DOUBLE PRECISION type has exactly twice the capacity of the REAL data type for both mantissa and exponent. (In case you've forgotten what you learned in high school, you can represent any number as a mantissa multiplied by ten raised to the power given by an exponent. You can write 6,626, for example, as 6.626E3. The number 6.626 is the mantissa, which you multiply by ten raised to the third power; in that case, 3 is the exponent.)

You gain no benefit by representing numbers that are fairly close to 1 (such as 6,626 or even 6,626,000) with an approximate NUMERIC data type. Exact numeric types work just as well — and after all, they’re exact. For numbers that are either very near 0 or much larger than 1, however, such as 6.626E-34 (a very small number), you must use an approximate NUMERIC type. Exact NUMERIC data types can't hold such numbers. On other systems, the DOUBLE PRECISION type gives you somewhat more than twice the mantissa capacity — and somewhat less than twice the exponent capacity as the REAL type. On yet another type of system, the DOUBLE PRECISION type gives double the mantissa capacity but the same exponent capacity as the REAL type. In this case, accuracy doubles, but range does not.

The SQL specification doesn't try to dictate, arbitrate, or establish by fiat what DOUBLE PRECISION means. The specification requires only that the precision of a DOUBLE PRECISION number be greater than the precision of a REAL number. Although this constraint is rather weak, it's probably the best possible, given the great differences you encounter in hardware.

FLOAT data type

The FLOAT data type is most useful if you think that you may someday migrate your database to a hardware platform with register sizes different from those available on your current platform. By using the FLOAT data type, you can specify a precision — for example, FLOAT (5). If your hardware supports the specified precision with its single-precision circuitry, then your present system uses single-precision arithmetic. If, after you migrate your database, the specified precision requires double-precision arithmetic, then the system uses double-precision arithmetic.

Using FLOAT rather than REAL or DOUBLE PRECISION makes moving your databases to other hardware easier. That's because the FLOAT data type enables you to specify precision and lets the hardware fuss over whether to use single- or double-precision arithmetic. (Remember, the precision of REAL and DOUBLE PRECISION numbers is hardware-dependent.)

If you aren't sure whether to use the exact NUMERIC data types (that is, NUMERIC and DECIMAL) or the approximate NUMERIC data types (that is, FLOAT and REAL), use the exact NUMERIC types. Exact data types demand fewer system resources — and, of course, give exact (rather than approximate) results. If the range of possible values of your data is large enough to require you to use approximate data types, you can probably determine this fact in advance.

CHARACTER data type

If you define the data type of a column as CHARACTER or CHAR, you can specify the number of characters the column holds by using the syntax CHAR (x), where x is the number of characters. If you specify a column's data type as CHAR (16), for example, the maximum length of any data you can enter in the column is 16 characters. If you don’t specify an argument (that is, you don’t provide a value in place of the x, SQL assumes a field length of one character. If you enter data into a CHARACTER field of a specified length and you enter fewer characters than the specified number, SQL fills the remaining character spaces with blanks.

CHARACTER VARYING data type

The CHARACTER VARYING data type is useful if entries in a column can vary in length but you don't want SQL to pad the field with blanks. This data type enables you to store exactly the number of characters that the user enters. No default value exists for this data type. To specify this data type, use the form CHARACTER VARYING (x) or VARCHAR (x), where x is the maximum number of characters permitted.

CHARACTER LARGE OBJECT data type

The CHARACTER LARGE OBJECT (CLOB) data type was introduced with SQL:1999. As its name implies, it's used with huge character strings that are too large for the CHARACTER type. CLOBs behave much like ordinary character strings, but there are restrictions on what you can do with them.

For one thing, a CLOB may not be used in a PRIMARY KEY, FOREIGN KEY, or UNIQUE predicate. Furthermore, it may not be used in a comparison other than one for either equality or inequality. Because of their large size, applications generally do not transfer CLOBs to or from a database. Instead, a special client-side data type called a CLOB locator is used to manipulate the CLOB data. It's a parameter whose value identifies a large character-string object.

A predicate is a statement that may either be logically True or logically False.

NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT data types

Various languages have some characters that differ from any characters in another language. For example, German has some special characters not present in the English-language character set. Some languages, such as Russian, have a very different character set from that of English. For example, if you specify the English character set as the default for your system, you can use alternative character sets because the NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT data types function the same as the CHARACTER, CHARACTER VARYING, and CHARACTER LARGE OBJECT data types — the only difference is that the character set you're specifying is different from the default character set.

You can specify the character set as you define a table column. If you want, each column can use a different character set. The following example of a table-creation statement uses multiple character sets:

CREATE TABLE XLATE (

LANGUAGE_1 CHARACTER (40),

LANGUAGE_2 CHARACTER VARYING (40) CHARACTER SET GREEK,

LANGUAGE_3 NATIONAL CHARACTER (40),

LANGUAGE_4 CHARACTER (40) CHARACTER SET KANJI

) ;

Here the LANGUAGE_1 column contains characters in the implementation’s default character set. The LANGUAGE_3 column contains characters in the implementation's national character set. The LANGUAGE_2 column contains Greek characters. And the LANGUAGE_4 column contains Kanji characters. After a long absence, Asian character sets, such as Kanji, are now available in many DBMS products.

BINARY data type

If you define the data type of a column as BINARY, you can specify the number of bytes (octets) the column holds by using the syntax BINARY (x), where x is the number of bytes. If you specify a column's data type as BINARY (16), for example, the binary string must be 16 bytes in length. BINARY data must be entered as bytes, starting with byte one.

BINARY VARYING data type

Use the BINARY VARYING or VARBINARY type when the length of a binary string is a variable. To specify this data type, use the form BINARY VARYING (x) or VARBINARY (x), where x is the maximum number of bytes permitted. The minimum size of the string is zero and the maximum size is x.

BINARY LARGE OBJECT data type

The BINARY LARGE OBJECT (BLOB) data type is used with huge binary strings that are too large for the BINARY type. Graphical images and music files are examples of huge binary strings. BLOBs behave much like ordinary binary strings, but SQL puts some restrictions on what you can do with them.

For one thing, you can't use a BLOB in a PRIMARY KEY, FOREIGN KEY, or UNIQUE predicate. Furthermore, no BLOBs are allowed in comparisons other than those for equality or inequality. BLOBs are large, so applications generally don't transfer actual BLOBs to or from a database. Instead, they use a special client-side data type called a BLOB locator to manipulate the BLOB data. The locator is a parameter whose value identifies a binary large object.

DATE data type

The DATE type stores year, month, and day values of a date, in that order. The year value is four digits long, and the month and day values are both two digits long. A DATE value can represent any date from the year 0001 to the year 9999. The length of a DATE is ten positions, as in 1957-08-14.

TIME WITHOUT TIME ZONE data type

The TIME WITHOUT TIME ZONE data type stores hour, minute, and second values of time. The hours and minutes occupy two digits. The seconds value may be only two digits but may also expand to include an optional fractional part. Therefore, this data type can represent a time such as (for example) 32 minutes and 58.436 seconds past 9:00 a.m. as 09:32:58.436.

The precision of the fractional part is implementation-dependent but is at least six digits long. A TIME WITHOUT TIME ZONE value takes up eight positions (including colons) when the value has no fractional part, or nine positions (including the decimal point) plus the number of fractional digits when the value does include a fractional part. You specify TIME WITHOUT TIME ZONE type data either as TIME, which gives you the default of no fractional digits, or as TIME WITHOUT TIME ZONE (p), where p is the number of digit positions to the right of the decimal. The example in the preceding paragraph represents a data type of TIME WITHOUT TIME ZONE (3).

TIMESTAMP WITHOUT TIME ZONE data type

TIMESTAMP WITHOUT TIME ZONE data includes both date and time information. The lengths and the restrictions on the values of the components of TIMESTAMP WITHOUT TIME ZONE data are the same as they are for DATE and TIME WITHOUT TIME ZONE data, except for one difference: The default length of the fractional part of the time component of a TIMESTAMP WITHOUT TIME ZONE is six digits rather than zero.

If the value has no fractional digits, the length of a TIMESTAMP WITHOUT TIME ZONE is 19 positions — ten date positions, one space as a separator, and eight time positions, in that order. If fractional digits are present (six digits is the default), the length is 20 positions plus the number of fractional digits. The 20th position is for the decimal point. You specify a field as TIMESTAMP WITHOUT TIME ZONE type by using either TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE (p), where p is the number of fractional digit positions. The value of p can't be negative, and the implementation determines its maximum value.

TIME WITH TIME ZONE data type

The TIME WITH TIME ZONE data type is the same as the TIME WITHOUT TIME ZONE data type except this type adds information about the offset from Universal Time (UTC, the successor of Greenwich Mean Time or GMT). The value of the offset may range anywhere from –12:59 to +13:00. This additional information takes up six additional digit positions following the time — a hyphen as a separator, a plus or minus sign, and then the offset in hours (two digits) and minutes (two digits) with a colon in between the hours and minutes. A TIME WITH TIME ZONE value with no fractional part (the default) is 14 positions long. If you specify a fractional part, the field length is 15 positions plus the number of fractional digits.

TIMESTAMP WITH TIME ZONE data type

The TIMESTAMP WITH TIME ZONE data type functions the same as the TIMESTAMP WITHOUT TIME ZONE data type except that this data type also adds information about the offset from Universal Time. The additional information takes up six additional digit positions following the timestamp. (See the preceding section for the form of the time-zone information.) Including time-zone data sets up 25 positions for a field with no fractional part and 26 positions (plus the number of fractional digits) for fields that do include a fractional part. (Six is the default number of fractional digits.)

ARRAY type

The ARRAY data type violates first normal form (1NF), but in a different way than the way the ROW type violates 1NF. The ARRAY type, a collection type, is not a distinct type in the same sense that CHARACTER and NUMERIC are distinct data types. An ARRAY type merely allows one of the other types to have multiple values within a single field of a table. For example, say your organization needs to be able to contact customers whether they're at work, at home, or on the road. You want to maintain multiple telephone numbers for them. You can do this by declaring the Phone attribute as an array, as shown in the following code:

CREATE TABLE CUSTOMER (

CustID INTEGER PRIMARY KEY,

LastName CHARACTER VARYING (25),

FirstName CHARACTER VARYING (20),

Address addr_typ,

Phone CHARACTER VARYING (15) ARRAY [3]

) ;

The ARRAY [3] notation allows you to store up to three telephone numbers in the CUSTOMER table. The three telephone numbers represent an example of a repeating group. Repeating groups are a no-no according to classical relational database theory, but this is one of several examples of cases where SQL:1999 broke the rules. When Dr. Codd first specified the rules of normalization, he traded off functional flexibility for data integrity. SQL:1999 took back some of that functional flexibility, at the cost of some added structural complexity.

The increased structural complexity could translate into compromised data integrity if you are not fully aware of all the effects of the actions you perform on your database. Arrays are ordered, in that each element in an array is associated with exactly one ordinal position in the array.

An array is an ordered collection of values, and the cardinality of an array is the number of elements in the array. An SQL array can have any cardinality from zero up to and including some declared maximum number of elements. This means that the cardinality of a column of the array type can vary from one row to the next. An array can be atomically null, in which case its cardinality would also be null. A null array is not the same as an empty array, whose cardinality would be zero. An array that has only null elements would have a cardinality greater than zero. For example, an array with five null elements would have a cardinality of five.

If an array has a cardinality that is less than the declared maximum, the unused cells in the array are considered to be nonexistent. They are not considered to contain null values; they just aren't there at all.

You can access individual elements in an array by enclosing their subscripts in square brackets. If you have an array named Phone, then Phone [3] would refer to the third element of the Phone array.

Since SQL:1999, it has been possible to find out the cardinality of an array by invoking the CARDINALITY function. SQL:2011 added the ability to discover the maximum cardinality of an array by using the ARRAY_MAX_CARDINALITY function. This is very useful because it enables you to write general-purpose routines that apply to arrays with different maximum cardinalities. Routines with hard-coded maximum cardinalities apply only to arrays that have a given maximum cardinality and would have to be rewritten for arrays of any other maximum cardinality.

Whereas SQL:1999 introduced the ARRAY data type and the ability to address individual elements within an array, it did not make any provision for removing elements from an array. That oversight was corrected in SQL:2011 with the introduction of the TRIM_ARRAY function, which enables you to remove elements from the end of an array.

MULTISET type

A multiset is an unordered collection. Specific elements of the multiset may not be referenced; usually that's because those elements are not assigned specific ordinal positions in the multiset.

Distinct types

Distinct types are the simpler of the two forms of user-defined types. A distinct type’s defining feature is that it’s expressed as a single data type. It is constructed from one of the predefined data types, called the source type. Multiple distinct types that are all based on a single source type are distinct from each other; thus, they are not directly comparable. For example, you can use distinct types to distinguish between different currencies. Consider the following type definition:

CREATE DISTINCT TYPE USdollar AS DECIMAL (9,2) ;

This definition creates a new data type for U.S. dollars (USdollar), based on the predefined DECIMAL data type. You can create another distinct type in a similar manner:

CREATE DISTINCT TYPE Euro AS DECIMAL (9,2) ;

You can now create tables that use these new types:

CREATE TABLE USInvoice (

InvID INTEGER PRIMARY KEY,

CustID INTEGER,

EmpID INTEGER,

TotalSale USdollar,

Tax USdollar,

Shipping USdollar,

GrandTotal USdollar

) ;

CREATE TABLE EuroInvoice (

InvID INTEGER PRIMARY KEY,

CustID INTEGER,

EmpID INTEGER,

TotalSale Euro,

Tax Euro,

Shipping Euro,

GrandTotal Euro

) ;

The USdollar type and the Euro type are both based on the DECIMAL type, but instances of one cannot be directly compared with instances of the other or with instances of the DECIMAL type. In SQL, as in the real world, it is possible to convert U.S. dollars into euros, but doing so requires a special operation (CAST). After conversion is complete, comparisons are possible.

Structured types

The second form of user-defined type — the structured type — is expressed as a list of attribute definitions and methods instead of being based on a single predefined source type.

User-defined types sourced from collection types

In the earlier section “Distinct types,” I illustrate how you can create a user-defined type from a predefined type, using the example of creating a USDollar type from the DECIMAL type. This capability was introduced in SQL:1999. SQL:2011 expanded on this capability by enabling you to create a new user-defined type from a collection type. This enables the developer to define methods on the array as a whole, not just on the individual elements of the array, as allowed by SQL:1999.

What the server is

The server (short for database server) is the part of a client/server system that holds the database. The server also holds the server software — the part of a database management system that interprets commands coming in from the clients and translates these commands into operations in the database. The server software also formats the results of retrieval requests and sends the results back to the requesting client.

What the server does

The server’s job is relatively simple and straightforward. All a server needs to do is read, interpret, and execute commands that come to it across the network from clients. Those commands are in one of several data sublanguages.

A sublanguage doesn’t qualify as a complete language — it implements only part of a language. A data sublanguage may, for example, deal only with data handling. The sublanguage has operations for inserting, updating, deleting, and selecting data, but may not have flow control structures such as DO loops, local variables, functions, procedures, or input/output to printers. SQL is the most common data sublanguage in use today and has become an industry standard. In fact, SQL has supplanted proprietary data sublanguages on machines in all performance classes. With SQL:1999, SQL acquired many of the features missing from traditional sublanguages. However, SQL is still not a complete general-purpose programming language; it must be combined with a host language to create a database application.

What the client is

The client’s primary job is to provide a user interface. As far as the user is concerned, the client machine is the computer, and the user interface is the application. The user may not even realize that the process involves a server. The server is usually out of sight — often in another room. Aside from the user interface, the client also contains the application program and the client part of the DBMS. The application program performs the specific task you require (say, in accounts receivable or order entry). The client part of the DBMS executes the application program’s commands and exchanges data and SQL data-manipulation commands with the server part of the DBMS.

What the client does

The client part of a DBMS displays information onscreen and responds to user input transmitted via the keyboard, mouse, or other input device. The client may also process data coming in from a telecommunications link or from other stations on the network. The client part of the DBMS does all the application-specific “thinking.” To a developer, the client part of a DBMS is the interesting part. The server part just handles the requests of the client part in a repetitive, mechanical fashion.