SQL - Data Types

---

---

What are SQL Data types?

An SQL data type refers to the type of data which can be stored in a column of a database table. In a column, the user can store numeric, string, binary, etc by defining data types. For example integer data, character data, monetary data, date and time data, binary strings, and so on.

While creating a database table in a database, we need to specify following two attributes to define a table column:

• Name of the column
• Data type of the column

A database table's column defines the data, whereas database table rows populate data into the table.

For example, if you want to store student name in a column then you should give column name something like student_name and it's data type will be char(50) which means it can store a string of characters up to 50 characters.

The data type provide guidelines for SQL to understand what type of data is expected inside each column, and hence, prevents the user from entering any unexpected or invalid data in a column. For example, if we want a column to store only integer values, we can specify its data types as INT. SQL will show an error if any other value apart from an integer is inserted into that particular column.

Different RDBMS supports different type of data types to define their tables. This SQL Data types tutorial will list down different data types available in MySQL, Oracle, MS SQL Server, and MS Access databases.

Defining a Data Type

SQL Data types are defined during the creation of a table in a database. While creating a table, it is required to specify its respective data type and size along with the name of the column.

Following is the syntax to specify a data type in MySQL −

CREATE TABLE table_name(column1 datatype, column2 datatype....)

Let us look at an example query below to understand better.

CREATE TABLE Customers (Name VARCHAR (25), Age INT);

In the above SQL query, we are creating a table Customers. And since the Name column only stores string values, we are specifying its data type as "VARCHAR". The VARCHAR data type represents string values in SQL. Similarly, we define the Age column with the integer data type, "INT".

When we assign the appropriate data type to a column, we can make efficient use of memory by allocating only the amount of system memory required for the data in the relevant column.

Types of SQL Data Types

There are three main types of SQL data types available in any RDBMS. They are listed below −

• String
• Numeric
• Date and Time

Data Types in MySQL, SQL Server, Oracle, and MS Access Databases

Let's discuss the data types in MySQL, SQL Server, Oracle (PL/SQL) and MS Access Databases in detail. All the three main types (String, Numeric, Date and Time) of SQL data types explained below-

MySQL Data Types

There are three main data types available in MySQL Database: string, numeric, and date and time. Following section lists down all the data types available in MySQL Version 8.0

MySQL - String Data Types

MySQL - Numeric Data Types

MySQL - Date and Time Data Types

MS SQL Server Data Types

As we have previously discussed in this chapter, there are three main data types in MS SQL server. They are: string, numeric, and date and time.

MS SQL Server - String Data Types

String data types in SQL allow us to store a group of characters, enclosed in single quotes, in a record of a table column. These characters can be of any type: numerals, letters, symbols etc.

Users can either store a fixed number of characters or a variable number of characters, depending on their preferences.

Following is the list of the data types that are included under the string data types in SQL.

Example

In the following example, we are creating a table "students" with only string data types values: varchar and char.

CREATE TABLE students (
  name varchar(20) NOT NULL,
  gender char(6) NOT NULL,
  city text NOT NULL
);

Output

On executing the query, the output will be displayed as −

(0 rows affected)

Verification

On the execution of the SQL queries "EXEC sp_help 'dbo.students';" we get the details of the table and the data types of each column.

MS SQL Server - Numeric Data Types

Numeric data types are one of the most widely used data types in SQL. They are used to store numeric values only.

Following is the list of data types that are included under the numeric data types in SQL.

Example

In the following example, we are creating a table named employees with only numeric data type values.

CREATE TABLE employees (
  ID int NOT NULL,
  myBoolean bit NOT NULL,
  Fee money,
  celsius float NOT NULL
);

Output

On executing the query, the output will be displayed as −

(0 rows affected)

Verification

On the execution of the SQL queries "EXEC sp_help 'dbo.employees;" we get the details of the table and the data types of each column.

MS SQL Server - Date and Time Data Types

datetime data types are used in SQL for values that contain both dates and times. datetime and time values are defined in the formats: yyyy-mm-dd, hh:mm:ss.nnnnnnn (n is dependent on the column definition) respectively.

Following is the list of data types that are included under the date and times data types in SQL.

Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

Example

In the following example, we are creating a table named Cust_details with only date and time data types values.

CREATE TABLE Cust_details (
  HolidayDate DATE NOT NULL,
  OrderDateTime DATETIME,
  ScheduleFrom TIME NOT NULL,
  ShippingDateTime DATETIME2
);

Output

On executing the query, the output will be displayed as −

(0 rows affected)

Verification

On the execution of the SQL queries "EXEC sp_help 'dbo.Cust_details;" we get the details of the table and the data types of each column.

Note:

• If you are using the MySQL workbench to run the SQL data types and their queries, then there are some SQL data types and formats for date and time that won't work; like "money", "datetime2", "yyyy/mm/dd" and "time AM". All these data types specified are compatible only with the SQL server.
• The size of these data types may change in the future updates keep checking the SQL documentation.

Oracle Data Types

There are four main types of data types available in Oracle Database: string, numeric, date & time and large object data types. Following section lists down all the data types available in Oracle Database.

Oracle - String Data Types

Oracle - Numeric Data Types

Oracle - Date and Time Data Types

Oracle - Large Object Data Types (LOB Types)

MS Access Data Types

The MS Access database also offers four categories of data types: String, Numeric, Date and Time, and other specialized data types.

Following are all data types that are provided by MS Access 2013 version and later.

MS Access - String Data Types

MS Access - Numeric Data Types

MS Access - Date and Time Data Types

MS Access - Specialized Data Types