SQL Data Types (Fully Described)

In this chapter you will learn what data is, its characteristics and the various types that the SQL programming language supports. There are different general types of data that are further categorized into several subtypes. However, it is advisable that you use defined data types to ensure the portability and comprehensibility of your SQL Server database.

Definition of Data

Since database is a collection of information, it can store names, numbers, images, calculations, financial amounts, characters and so on. This stored information is what you call data, which you can change or manipulate anytime you want. When you start providing rules on how you write and store data, then you are dealing with data types. Data types take into consideration the length allocated by the database for every column in the table and what values it could contain - whether it is alphanumeric, just numbers, graphics, date or time. By defining what data is stored in each database field, you are preventing the occurrence of data entry errors. This form of validation that controls intcorrect data to be entered into the database is also called field definition.

Each database field will have a specific value if it contains a data item. There are times, however, that a certain field does not have any data item at all. In this case, the field’s value is considered null meaning the value is not known. This null value is different from the numeric zero value or the blank character value, since zeroes and blanks are definite values. The following are scenarios when you may have a null value:

1. You don’t know what the value is yet even if it possibly exists.


2. The value does not exist yet.


3. The value is out of range.


4. The field is not applicable for a particular row.

Types of Data

The following are the general data types predefined in the SQL language (that are further categorized into subtypes):

Numeric – The value defined by the numeric data type is some kind of a number, which could either be expressed with an exact or just an approximate value.

Exact Numeric

• INTEGER– This consists only of whole numbers that are both positive and negative. It does not contain a decimal nor a fractional part. The value ranges from -2,147,483,648 to 2,147,483,647, with an allocated 4 bytes of storage size.


• SMALLINT –This is used in replacement of integers to save storage space, but with a precision that cannot be larger than that of an integer. Precision in computer programming is the maximum total of significant digits a number can have. The value ranges from -32,768 to 32,767, with an allocated 2 bytes of storage size.


• BIGINT – This is the reverse of the SMALLINT, where its minimum precision is the same as the INTEGER data type or greater. The value ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, with an allocated 8 bytes of storage size.


• NUMERIC(p, s) – In addition to the integer part, this data type also contains a fractional component that indicates the precision and scale of the value. Scale is the number of digits or places reserved in a fractional part of the data, located at the right side of the decimal point. In NUMERIC (p, s), ‘p’ specifies the precision while ‘s’ is for the scale. For example, NUMERIC (6, 3) means that the number’s absolute value will only be up to 999.999 (6 total significant digits with 3 digits following the decimal point).


• DECIMAL (p, s) – Like the NUMERIC data type, this has a fractional component where you can specify both the value precision and scale. However, this data type allows greater precision. For example, DECIMAL (6, 3) can contain values up to 999.999 but the database will still accept values larger than 999.999. Let us say you entered the number 123.4564, this will be rounded off to 123.456. The allocated storage size for this data type is based on the given precision.

Approximate Numeric :

• REAL (s) – This consists of a single-precision, floating-point number where the decimal point can “float” to different places in the said number. This means that this data type’s decimal value has a limitless precision and a scale of variable lengths. For example, the values for π (pi) can include 3.14159, 3.14 and 3.1 (each value has its own precision). For single precision, floating point numbers, their precision is between 1 and 21 inclusive. It also allocates 4 bytes of storage size for its values.


• DOUBLE PRECISION (p, s) – This consists of a double-precision, floating-point number and the capacity is twice of the REAL data type. This data type comes in handy when you require more precise numbers, like in most scientific field of disciplines. For double-precision, floating point numbers, their precision is between 22 and 53 inclusive. It also allocates 8 bytes of storage size for its values.


• FLOAT (p, s) – This is the data type that allows you to specify the precision and lets the computer decide whether you will go for a single- or a double-precision. It actually pertains to both REAL and DOUBLE PRECISION, depending on the precision you have specified. Because of this characteristic, it is easier to move the database from one computer platform to another.

String – The string data type stores alphanumeric information and is also considered as one of the most commonly used data types.

• CHARACTER (n)orCHAR (n) – This data type is also known as a fixed-length string or a constant character. This means that all the strings stored in that particular column have the same length, which is represented by ‘n’ (the number of characters or the maximum allocated length for the defined field). For example, if you set the column’s data type to CHAR (23) then the maximum length of any data entered in the field is 23 characters. If the string’s length is less than 23, then SQL fills the remaining spaces with blanks. This is the drawback of using fixed-length strings because storage space is wasted. On the other hand, if there is no value provided for ‘n’, then SQL assumes a length of one character. The maximum length for the CHARACTER data type is 254.


• CHARACTER VARYING (n)orVARCHAR (n) – This is used when the data entries are of different lengths, or not constant, but you don’t want SQL to fill the remaining spaces with blanks. Thus, the exact number of characters you enter will be stored in the database - further saving storage space. This data type has no default value and its maximum length is 32,672 characters.


• CHARACTER LARGE OBJECT (CLOB) – Introduced in SQL:1999, this variable-length data type is used to contain unicode character-based information that is too big to be stored as a CHARACTER type, such as large documents. The maximum value of a CLOB is up to 2,147,483,647 characters long.

Date and Time – This data type manages any information concerning dates and times.

• DATE – This data type provides storage for the year, month and day values of a date, in that particular order. The year value is expressed using four digits, which can be represented by any value ranging from 0001 up to 9999. As for the month and day values, they are both expressed using two digits. The format for the date data type is yyyy-mm-dd.


• TIME – This data type stores and displays time values with an hour-minutesecond format (“HH:MM:SS”).


• DATETIME – When the value contains both date and time information then you use the DATETIME data type, which is displayed using the “YYYY-MM-DD HH:MM:SS” format. The valid range of values for this type is from “1000-01-01 00:00:00” to “9999-12-31 23:59:59”.


• TIMESTAMP – This is similar to the DATETIME data type but the range of values is from “1970-01-01 00:00:01” UTC to “2038-01-19 03:14:07” UTC.

Boolean – This data type consists of values that are used for data comparison: TRUE, FALSE, or NULL. For data to be returned, all the conditions of the specified criteria for a given query should be met – meaning the Boolean value is TRUE. If data is not returned, then the value is either FALSE or NULL.

User-Defined Data Type

After learning the general pre-defined data types, you will now move to user-defined data types or simply UDTs. By the name itself, these are the data values that the user defines or specifies based on the existing data types. Thus, customization is allowed to maximize storage space and meet other user requirements. Furthermore, database application development becomes more flexible for programmers. This means that you can use UDTs when you need to enter the same type of data in a column that will be defined in several tables. To define UDTs, you can use the CREATE TYPE statement.