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, like, INT, CHAR, MONEY, DATETIME etc. They provide guidelines 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 invalid 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 listed 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 list of the data types that are included under the string data types in SQL.

S.No.	Datatypes & Description	Max Size	Storage
1	char(n) It holds the character string with the fixed width.	8000 characters	Defined Width
2	varchar(n) It holds the character string with the variable width.	8000 characters	2 bytes + number of chars
3	varchar(max) It holds the character string with the variable width.	1073741824 characters	2 bytes + number of chars
4	text It holds the character string with the variable width.	2GB of text data	4 bytes + number of chars
5	nchar It holds the Unicode string with the fixed width.	4000 characters	Defined width x 2
6	nvarchar It holds the Unicode string with the variable width.	4000 characters
7	ntext It holds the Unicode string with the variable width.	2 GB of text data
8	binary(n) It holds the binary string with the fixed width.
9	varbinary It holds the binary string with variable width.	8000 bytes
10	varbinary(max) It holds the binary string of max length of variable width.	2 GB
11	image It holds the variable length of the data type that can store binary data.	2 GB
12	Nvarchar(max) It holds the Unicode string of max length of variable width.	536870912 characters.

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 list of data types that are included under the numeric data types in SQL.

S.No.	Datatype & Description	Storage
1	bit It holds the integer that can be 0, 1 or NULL.
2	tinyint It allow to holds the whole number from 0 to 255.	1 byte
3	smallint It allow to holds the number between -32,768 and 32,767.	2 bytes
4	int It allow to holds the whole number between -2,147,483,648 and 2,147,483,647.	4 bytes
5	bigint It allow to holds the whole number between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
6	decimal(p, s) It is fixed precision and scale numbers. That allow numbers from -10^38 + 1 to 10^38-1. The p parameter indicates the maximum total number of digits that can be stored on both sides of the decimal point, left and right. It must have a value from 1 to 38. By default, it is 18. The s parameter indicates the maximum number of the digit to the right of the decimal point. S must be a value from 0 to p. The value is set to 0 by default.	5 - 17 bytes
7	numeric(p, s) It is fixed precision and scale numbers. That allow numbers from -10^38 + 1 to 10^38-1. The p parameter indicates the maximum total number of digits that can be stored on both sides of the decimal point, left and right. It must have a value from 1 to 38. By default, it is 18. The s parameter indicates the maximum number of the digit to the right of the decimal point. S must be a value from 0 to p. The value is set to 0 by default.	5 - 17 bytes
8	smallmoney It holds the monetary data from -214,748.3648 to 214,748.3647.	4 bytes
9	Money It holds the monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.	8 bytes
10	Float(n) It holds or store the floating precession number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. Float(24) contains a 4-byte field, while Float(53) contains an 8-byte field. The default value of n is 53.	4 or 8 bytes
11	real It holds the floating precision number data from -3.40E + 38 to 3.40E + 38.	4 bytes

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

S.No.	Datatype & Description	Storage
1	datetime It stores date and time both from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.	8 bytes
2	datetime2 It stores date and time both from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.	6 - 8 bytes
3	smalldatetime It stores date and time both from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.	4 bytes
4	date It stores date only from January 1, 0001 to December 31 9999	3 bytes
5	time It store time only to an accuracy of 100 nanoseconds.	3 - 5 bytes
6	datetimeoffset It is the same of the datetime2 with the addition of the time zone offset.	8 - 10 bytes
7	timestamp It stores the unique number that gets updated every time a row gets created or modified. It does not correspond to real time and is based on internal time. Each table may have only one timestamp variable.

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

+------------------+-----------+----------+--------+-------+-------+----------+
| Column_name      | Type      | Computed | Length | Prec  | Scale | Nullable |
+------------------+-----------+----------+--------+-------+-------+----------+
| HolidayDate      | 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; like "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.