w3resource logo


SQL Data Types

SQL Data Types

Secondary Nav

Data Types

A data type is a set of representable values. Every representable value belongs to at least one data type and some belong to several data types. SQL supports three sorts of data types: predefined data types, constructed types, and user-defined types.
Predefined data types are sometimes called the "built-in data types", though not in this International Standard. Every predefined data type is a subtype of itself and of no other data types. It follows that every predefined data type is a supertype of itself and of no other data types.
User-defined data types can be defined by a standard, by an implementation, or by an application.
A constructed type is specified using one of SQL's data type constructors, ARRAY, REF, and ROW. The type is either an array type, a reference type or a row type, according to whether it is specified with ARRAY, REF, or ROW, respectively. Array types are the only examples of constructed types known generically as collection types.

Data types are used within the CREATE TABLE statement as part of column definitions :

CREATE TABLE <tablename>(   
<column_name> <data_type> ... ,   
<column_name> <data_type> ... ,   
... );

What is Unicode?
According to unicode.org "Unicode provides a unique number for every character, no matter what the platform, no matter what the program, no matter what the language. The Unicode Standard has been adopted by such industry leaders as Apple, HP, IBM, JustSystems, Microsoft, Oracle, SAP, Sun, Sybase, Unisys and many others. Unicode is required by modern standards such as XML, Java, ECMAScript (JavaScript), LDAP, CORBA 3.0, WML, etc., and is the official way to implement ISO/IEC 10646. It is supported in many operating systems, all modern browsers, and many other products. The emergence of the Unicode Standard and the availability of tools supporting it are among the most significant recent global software technology trends.
Incorporating Unicode into client-server or multi-tiered applications and websites offers significant cost savings over the use of legacy character sets. Unicode enables a single software product or a single website to be targeted across multiple platforms, languages and countries without re-engineering. It allows data to be transported through many different systems without corruption."

Examples of SQL Data Types :

Literal Examples
Character string '59', 'Python'
Numeric 48, 10.34, 2., .001, -125, +5.33333, 2.5E2, 5E-3
Boolean TRUE, FALSE, UNKNOWN
Datetime DATE, '2016-05-14', TIME '04:12:00',TIMESTAMP ‘2016-05-14 10:23:54’
Interval INTERVAL ‘15-3’ YEAR TO MONTH, INTERVAL ‘23:06:5.5’ HOUR TO SECOND

SQL Data Types : New Features in SQL 2003 standard

  • New data types
    • BIGINT
    • MULTISET
  • Extensions to existing data types
    • Unbounded ARRAY
  • Deletion of existing types
    • BIT
    • BIT VARYING

Here we have discussed SQL 2003 standard data types with a short description and example.

SQL Data Types with example and explanation

Contents:

Character String Types :

A character string data type is described by a character string data type descriptor.

Data Type Description
CHARACTER Character string, fixed length.
A string of text in an implementer-defined format. The size argument is a single nonnegative integer that refers to the maximum length of the string. Values for this type must enclose in single quotes.
CHARACTER VARYING (VARCHAR) Variable length character string, maximum length fixed.
CHARACTER LARGE OBJECT (CLOB) A Character Large OBject (or CLOB) is a collection of character data in a database management system, usually stored in a separate location that is referenced in the table itself.
NATIONAL CHARACTER (NCHAR)
NATIONAL CHARACTER type is the same as CHARACTER except that it holds standardized multibyte characters or Unicode characters.
NATIONAL CHARACTER VARYING (NCHAR VARYING) NATIONAL CHARACTER VARYING type is the same as CHARACTER VARYING except that it holds standardized multibyte characters or Unicode characters.
NATIONAL CHARACTER LARGE OBJECT (NCLOB) NCLOB type is the same as CLOB except that it holds standardized multibyte characters or Unicode characters.

SQL: Fixed-length character string :

SQL Data Type fixed length character string

SQL : Character strings of Varying length

SQL Data Type : Varying length character string

Example : A table with columns of fixed and varying length size strings and a CLOB string

CREATE TABLE test (   
id    DECIMAL PRIMARY KEY,   
col1 CHAR(8),       -- exactly 8 characters   
col2 VARCHAR(100),   -- up to 100 characters   
col3 CLOB            -- very large strings 
);

DBMS Character String Types :

DBMS and version Types
MySQL 5.7 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET
PostgreSQL 9.5.3 CHARACTER VARYING(n), VARCHAR(n), CHARACTER(n), CHAR(n), TEXT
SQL Server CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT
Oracle 11g CHAR, VARCHAR2, VARCHAR, NCHAR, NVARCHAR2, CLOB, NCLOB, LONG

Boolean Type :

The data type boolean comprises the distinct truth values true and false .The boolean data type also supports the unknown truth value as the null value unless prohibited by a NOT NULL constraint.

Data Type Description
BOOLEAN Stores truth values - either TRUE or FALSE.

Example : A table using boolean type

CREATE TABLE test (   
id    DECIMAL PRIMARY KEY,   
col1 BOOLEAN
);

DBMS Boolean Types :

DBMS and version Types
MySQL 5.7 TINYINT(1)
PostgreSQL 9.5 BOOLEAN
SQL Server 2014 BIT
Oracle 11g BOOLEAN

Binary large object Type :

A binary string is a sequence of octets that does not have either a character set or collation associated with it and is described by a binary data type descriptor.

Data Type Description
BINARY LARGE OBJECT (BLOB). BLOB stores a long sequence of bytes.

Example : A table with columns of fixed and variable size binary data and a BLOB

CREATE TABLE test (   
id    DECIMAL PRIMARY KEY,   
col1  BINARY(8),      -- exactly 8 byte   
col2  VARBINARY(140), -- up to 140 byte   
col3  BLOB            -- very large data: store picture, sound ... 
);

DBMS Binary Types :

DBMS and version Types
MySQL 5.7 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
PostgreSQL 9.5 BYTEA
SQL Server 2014 BLOB
Oracle 11g LOB

Numeric Types :

Numeric data types represent numerical values.

Data Type Description
INTEGER Represents an integer. The minimum and maximum values depend on the DBMS.
SMALLINT Same as INTEGER type except that it might hold a smaller range of values, depending on the DBMS.
BIGINT Same as INTEGER type except that it might hold a larger range of values, depending on the DBMS.
DECIMAL(p, s) Exact numerical, precision p, scale s. A decimal number, that is a number that can have a decimal point in it. The size argument has two parts : precision and scale. The scale can not exceed the precision. Precision comes first, and a comma must separate from the scale argument.
NUMERIC(p, s) Exact numerical, precision p, scale s. The maximum precision depends on the DBMS.
FLOAT(p) Approximate numerical, mantissa precision p. Precision is greater than or equal to 1 and the maximum precision depends on the DBMS.
REAL Same as FLOAT type except that the DBMS defines the precision.
DOUBLE PRECISION Same as FLOAT type (DBMS defines the precision) but greater than that of REAL.

Example : Precision and Scale Examples for 235.89

SQL DATA TYPE Numeric precision and scale

Example : A table using numeric data types

CREATE TABLE test (   
id    DECIMAL PRIMARY KEY,   
name  VARCHAR(100),   -- up to 100 characters 
col1  DECIMAL(5,2),    -- three digits before the decimal and two behind   
col2  SMALLINT,        -- no decimal point   
col3  INTEGER,         -- no decimal point   
col4  BIGINT,           -- no decimal point. 
col5  FLOAT(2),        -- two or more digits after the decimal place   
col6  REAL,   
col7  DOUBLE PRECISION
);

DBMS Numeric Types :

DBMS and version Types
MySQL 5.7 INTEGER(TINYINT, SMALLINT, MEDIUMINT, INT BIGINT, INTEGER)
FIXED-POINT(DECIMAL, NUMERIC)
FLOATING-POINT(FLOAT, DOUBLE)
BIT-VALUE(BIT),
PostgreSQL 9.5.3 SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, SMALLSERIAL, SERIAL, BIGSERIAL
SQL Server 2014 EXACT NUMERICS(BIGINT, BIT, DECIMAL, INT, MONEY, NUMERIC, SMALLINT, SMALLMONEY, TINYINT)
APPROXIMATE NUMERICS(FLOAT, REAL )
Oracle 11g NUMBER
FLOATING-POINT(BINARY_FLOAT, BINARY_DOUBLE)

Datetime Types :

The datetime data types represent the date and time of day.

Data Type Description
DATE Represents a date. Format : yyyy-mm-dd
TIME WITHOUT TIME ZONE Represents a time of day without time zone. Format : hh:mm:ss
TIME WITH TIME ZONE Represents a time of day with time zone. Format : yyyy-mm-dd AT TIME ZONE -06:00.
TIMESTAMP WITHOUT TIME ZONE Represents a combination of DATE and TIME values separated by a space. Format : yyyy-mm-dd hh:mm:ss
TIMESTAMP WITH TIME ZONE Represents a combination of DATE and TIME values separated by a space with time zone. Format : yyyy-mm-dd hh:mm:ss AT TIME ZONE -06:00.

Example : A table using Datetime data types

CREATE TABLE test (   
id   DECIMAL PRIMARY KEY,   
col1 DATE,    -- store year, month and day (Oracle: plus hour, minute and seconds)   
col2 TIME,   
col3 TIMESTAMP(9), -- a timestamp with 9 digits after the decimal of seconds   
col4 TIMESTAMP WITH TIME ZONE   -- a timestamp including the name of a timezone   
);

DBMS Date and Time Types :

DBMS and version Types
MySQL 5.7 DATE, TIME, DATETIME, TIMESTAMP, YEAR
PostgreSQL 9.5.3 TIMESTAMP [ WITHOUT TIME ZONE ], TIMESTAMP WITH TIME ZONE, DATE, TIME[ WITHOUT TIME ZONE ], TIME WITH TIME ZONE,
SQL Server DATE, DATETIME2, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME
Oracle 11g DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

Interval Type :

It stores the quantity of time between two datetime values. For example between 10:00 and 12:30 is an interval of 02:30 (2 hours and 30 minutes). There are two classes of intervals. The first one called year-month intervals, includes no fields other than YEAR and MONTH, though not both are required. The other class called day-time intervals that can include any fields other than YEAR or MONTH.

Data Type Description
Year-month Includes no fields other than YEAR and MONTH, though not both are required.
Day-time Includes any fields other than YEAR or MONTH. These intervals can contain a day value, hour value, minute value, second value, or some combination thereof.

Fields in Year-month INTERVAL values :

Keyword Meaning
YEAR years
MONTH months

Fields in day-time INTERVAL values :

Keyword Meaning
DAY Days
HOUR Hours
MINUTE Minutes
SECOND Seconds and possibly fractions of a second

Valid values for fields in INTERVAL values :

Keyword Valid values of INTERVAL fields
YEAR Unconstrained except by <interval leading field precision>
MONTH Months (within years) (0-11)
DAY Unconstrained except by <interval leading field precision>
HOUR Hours (within days) (0-23)
MINUTE Minutes (within hours) (0-59)
SECOND Seconds (within minutes) (0-59.999...)

Valid operators involving datetimes and intervals :

Operand1 Operator Operand2 Result Type
Datetime - Datetime Interval
Datetime + or - Interval Datetime
Interval + Datetime Datetime
Interval + or - Interval Interval
Interval * or / Numeric Interval
Numeric * Interval Interval

Example : A table using Interval data types

CREATE TABLE test (   
id    DECIMAL PRIMARY KEY,   
col1 INTERVAL YEAR TO MONTH,   
col2 INTERVAL DAY TO SECOND(6)   -- an interval with 6 digits after the decimal of seconds
);

DBMS Interval Types :

DBMS and version Types
MySQL 5.7 NOT SUPPORTED
PostgreSQL 9.5 INTERVAL
SQL Server 2014 NOT SUPPORTED
Oracle 11g INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

XML Types

XML Stores XML data. It can be used wherever a SQL datatype is allowed, such as a column of a table.

Example : A table using XML data type

CREATE TABLE test(
id    DECIMAL PRIMARY KEY,   
col1  XML 
);

Collection Types :

COLLECTION
( ARRAY, MULTISET )
ARRAY(offered in SQL99) is a set-length and ordered a collection of elements, MULTISET (added in SQL2003) is a variable-length and unordered collection of elements. Both the elements must be of a predefined datatype.