SQL - Data Type

Data System Architecture

About

The Data Type of a column in SQL.

ANSI

They are defined in the ANSI/ISO SQL standard 1) but may vary from sql engine (database) to another.

The below table shows you the history of the data type in the ANSI standard.

Standard built-in types
SQL92 CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, and INTERVAL. See Sql92 doc
SQL99 BOOLEAN: Boolean (true or false) value
BLOB: Binary large object
CLOB: Character large object
SQL:2003 XML: XML object

Type

ANSI

The ansi data type of the database:

Sql Type Description (Range) Precision Jdbc Sql Type Constant
Fixed point Number (exact numeric)
NUMERIC fixed-point with precision and scale (up to 131072) and scale (up to 16383) NUMERIC
DECIMAL alias for numeric DECIMAL
Integer (exact numeric, with binary or decimal precision and scale of 0)
TINYINT Tiny Integer 0 to 255 (1 Bytes) TINYINT
SMALLINT Small Integer ±32768 (2 Bytes) SMALLINT
INTEGER Integer ±2147483648 (4 Bytes) INTEGER
BIGINT Big Integer ±9223372036854775808 (8 Bytes) BIGINT
Floating point Number (approximate numeric)
FLOAT precision may defined Single precision FLOAT
REAL precision is fixed Single precision (4 Bytes) REAL
DOUBLE PRECISION precision greater than REAL Double Precision DOUBLE
Text (String)
CHAR text padded with space CHAR
NCHAR text padded with space with character set CHAR
VARCHAR variable text VARCHAR
NVARCHAR variable text with character set NVARCHAR
CLOB large text stored in a file CLOB
Time
DATE Time at the day level DATE
TIMESTAMP Time at the ms level TIMESTAMP
TIME time part (hh:mm:ss) TIME
INTERVAL INTERVAL
Others
BLOB binary large object (binary data)
IDENTIFIER
STRUCT

User defined

Data Type may be defined/changed. This section shows some example of user defined data type.

Structured type

CREATE TYPE PLANE_POINT
AS (X FLOAT, Y FLOAT) NOT FINAL

DISTINCT type

User-defined type based on a built-in type; for example:

CREATE TYPE MONEY
AS NUMERIC(10,2) FINAL

Constructed types

A database may offers types based on a given base type such as:

  • base-type ARRAY[n]: Array of n base-type elements

Locators / Pointer / Reference

They are pointer to the location of data known also as REF(structured-type)

A locator typically refers to data that is too large to be included in the table such as images or audio.

A datalink is a type that creates a link with another database. Datalink values are part of the SQL ANSI/ISO standard specification (and of SQL MED - Management of External Data)





Discover More
Relational Data Model
(Relation|Table) - Tabular data

A Relation is a logical data structure composed of tuple (row) attribute (column, field) The following data structure are a relation: a table, a materialized view (query) (store data) a query,...
Data System Architecture
Float32 - 32-bit IEEE float (Single Precision)

32-bit IEEE float is a floating-point number encoded on 32 bit. float32 is also known simply as float Java Float Sql: Real, Float ...
Jdbc Class Architecture
JDBC - Data Type

The JDBC API defines standard mappings to convert SQL data types to JDBC data types and back. This includes support for SQL:2003 advanced data types such as BLOB, CLOB, ARRAY, REF, STRUCT, XML, and DISTINCT....
Card Puncher Data Processing
Oracle Database - Bytes or Characters for VARCHAR2 and CHAR

Text data is encoded/stored/transformed on the computer in bytes thanks to a character set that maps text to bytes. Historically, the character sets were single-byte character sets that could hold 256...
Relational Data Model
Relation - Column

A column is the relational model representation of a value set where the attribute is the column metadata that defines its name, type and others attributes properties The columns are located in the Y...
Data System Architecture
SQL - Char(N) datatype

Char(N) is a sql datatype that means that the column shall be able to hold any string of characters of a fixed length of N. If the string that a column of datatype char(N) holds is shorter than N characters,...
Data System Architecture
SQL - Character large object (CLOB)

A Character large object (CLOB) is a SQL data type used to store a large amount of character data. It's a specialized variant of large object (LOB) where data is stored in a file on the local file system...
Data System Architecture
SQL - Date datatype

A SQL DATE is a sql datatype that represents a date and contains therefore the s YEAR, MONTH, and DAY; See date...
Data System Architecture
SQL - Decimal(p,s) or Numeric(p,s)

DECIMAL and NUMERIC are sql datatype used to represent a fixed point notation (exact number) numeric is generally functionally identical to decimal. With a DECIMAL(5,2): the number has: a precision...
Data System Architecture
SQL - Double

SQL DOUBLE is a SQL data type that represent a floating point number with a double precision double precisionfloat(53)



Share this page:
Follow us:
Task Runner