SQL - Data Types

In general, a datatype is a classification of data which tells the compiler the type of data the programmer store. Most programming languages support various data types, such as integers, characters or strings, Booleans etc.

What are SQL datatypes?

Datatypes in SQL are used to define the type of data that can be stored in a column of a table, pke, INT, CHAR, MONEY, DATETIME etc. They provide guidepnes for SQL to understand what type of data is expected inside each column, and they also identify how SQL will interact with the stored data. The datatype specification, hence, prevents the user from entering any unexpected or invapd data.

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.

Types of Datatypes

There are three main types of datatypes in the SQL server. They are psted below −

String

Numeric

Date and Time

Each data type will be discussed later in this chapter.

Defining a Datatype

Datatypes are defined during the creation of a table in SQL. While creating a table, it is required to specify its respective datatype and size along with the name of the column.

The syntax to define a datatype on a column of an SQL table is −

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 query, we are creating a table Customer. And since the Name column only stores string values, we are specifying its datatype as “VARCHAR”. The VARCHAR datatype represents string values in SQL. Similarly, we define the Age column with the integer datatype, “INT”.

Note − 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.

Characteristics of Datatypes

Following are the various characteristics of datatypes in SQL −

The value of the datatypes may be fixed or variable.

The storage space depends on the value that is fixed length or variable length.

Datatypes in SQL Server

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

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 pst 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 datatypes of each column.

+--------------+---------+----------+--------+-------+-------+----------+
| Column_name  | Type    | Computed | Length | Prec  | Scale | Nullable |
+--------------+---------+----------+--------+-------+-------+----------+
| name         | varchar | no       | 20     |       |       | no       |
| gender       | char    | no       | 6      |       |       | no       |
| city         | text    | no       | 16     |       |       | no       |
+--------------+---------+----------+--------+-------+-------+----------+

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 pst 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 datatypes of each column.

+--------------+---------+----------+--------+-------+-------+----------+
| Column_name  | Type    | Computed | Length | Prec  | Scale | Nullable |
+--------------+---------+----------+--------+-------+-------+----------+
| ID           | int     | no       | 4      | 10    | 0     | no       |
| myBoolean    | bit     | no       | 1      |       |       | no       |
| Fee          | money   | no       | 8      | 19    | 4     | yes      |
| Celsius      | float   | no       | 8      | 53    | NULL  | no       |
+--------------+---------+----------+--------+-------+-------+----------+

Date and Time Data Types

datetime datatypes 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 pst of data types that are included under the date and times datatypes in SQL.

Note − Here, datetime has 3.33 milpseconds 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 (
   HopdayDate 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 datatypes of each column.

+------------------+-----------+----------+--------+-------+-------+----------+
| Column_name      | Type      | Computed | Length | Prec  | Scale | Nullable |
+------------------+-----------+----------+--------+-------+-------+----------+
| HopdayDate      | date      | no       | 3      | 10    | 0     | no       |
| OrderDateTime    | datetime  | no       | 8      |       |       | yes      |
| ScheduleFrom     | time      | no       | 5      | 16    | 7     | no       |
| ShippingDateTime | datetime2 | no       | 8      | 27    | 7     | yes      |
+------------------+-----------+----------+--------+-------+-------+----------+

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; pke "money", "datetime2", "yyyy/mm/dd" and "time AM". All these datatypes specified are compatible only with the SQL server.

The size of these datatypes may change in the future updates keep checking the SQL documentation.