SQL Data Types: Everything You Need To Know

Did you know that over 7 million developers use SQL around the world making it the second most popular programming language? 

The popularity of SQL grew after the American National Standards Institute (ANSI) adopted it in 1986. Hence, learning all about SQL, specifically its data types, their functions, and how they could be used with data has become important for every developer.

SQL data types are used to represent the nature of the data that can be stored in the database table. They are a classification or category of data based on the type of values that it represents, it includes numeric data types, character data types, boolean data types, and binary data types. In this blog, we will learn more about different SQL data types with examples.

SQL Command to Change Data Type

In order to update the SQL data types of a column, you can use the ALTER TABLE command with the ALTER COLUMN or MODIFY clause.  It will modify the data type depending on the database system you are using. As an example, consider the following:

ALTER TABLE my_table MODIFY my_column new_data_type;

You need to substitute “my_table” with the name of the table containing the column you want to change. Similarly, replace “my_column” with the name of the column you want to change and “new_data_type” with the new data type you want to give to the column.

SQL Data Types in DBMS

There is a wide variety of SQL data types that are supported by SQL Server, they are also known as SQL data types in DBMS. 

The following is a list of SQL data types with examples: 

Numeric Data Types

The numeric data type in SQL is used to store the values with a certain precision and scale. It is mainly written in integers or decimals. Following are the examples of numeric data types:

Data Type	Function
INT	It consists of integer values.
BIGINT	It has large integer values.
NUMERIC	It has the exact numeric value with a specified precision and scale.
REAL	It has approximate numeric values with floating-point precision.

Date and Time Data Types

The date and time data type in SQL iis used to store the values that denote the date and time. It is mainly used for expressing the date and time. Following are the examples of date and time data types:

Data Type	Function
DATE	It only consists of date values.
TIME	It only consists of time values.
DateTime	It consists of both date and time values.
SMALLDATETIME	It consists of the date and time values but with reduced precision.

Character and String Data Types

The character and string data type in SQL is used to store the values that have character or string values. It is used for expressing data of characters and strings. Following are the examples of character and string data types:

Data Type	Function
CHAR	It consists of fixed-length character strings.
VARCHAR	It consists of variable-length character strings.
TEXT	It consists of large variable-length character strings.

Unicode Character Data Types

This data type is more of a character encoding standard rather than a specific data type. It consists of a wide range of characters from various writing styles. The following are examples of Unicode character data types:

Data Type	Function
NCHAR	It has fixed-length Unicode character strings.
NVARCHAR	It has variable-length Unicode character strings.
NTEXT	It has large variable-length Unicode character strings.

Binary Data Types

The binary data type in SQL is used to specifically store the binary data. It includes audio files, images, and data that is not character based. The following are its examples: 

Data Type	Function
BINARY	This has fixed-length binary data.
VARBINARY	This has variable-length binary data.
IMAGE	This has large variable-length binary data.

 Miscellaneous Data Types

The miscellaneous data are the data types that are not specific characters or numbers. They provide a specialized data format for the storage of data. The following are some of its examples:

Data Type	Function
XML	This works with XML data.
SQL_VARIANT	It will allow the values of different data types to be stored as a single data type.
UNIQUE IDENTIFIER	It is a globally unique identifier (GUID).

There are different data types in SQL, you can check out this SQL course to get a clear understanding of the subject.

SQL Numeric Data Type

The numeric data types are used in SQL to store numeric values such as decimals, integers, and floating-point numbers. The following are the most commonly used numeric data types in SQL: 

NUMERIC/DECIMAL

 Decimal values are stored with precision and scale. The precision defines the total number of digits that can be stored, while the scale defines the number of digits stored after the decimal point.

INTEGER

For integer numbers, the SQL database server accepts the following types of data formats:

SQL Integer Data Type	Number of Bytes	Supported Range of Values
SMALLINT	2	Between -32767 and 32767
INT, INTEGER, SERIAL	4	Between -2,147,483,647 and 2,147,483,647
INT8, SERIAL8, BIGINT, BIGSERIAL	8	Between -9,223,372,036,854,775,807 and 9,223,372,036,854,775,807

FLOAT

This numeric data type is used to store floating-point values.

REAL

This numeric data type is used to store floating-point values in single-precision.

DOUBLE PRECISION

This numeric SQL data type is used to store floating-point values in double precision.

SQL BIT and Boolean Data Type

The BIT data type in SQL can store values from 1 to 64. Since the boolean data type is not a separate data type in SQL, the BIT data type is used to store boolean values. The BIT data type stores binary data that could possibly have two values between 0 and 1. It can also store flag values to indicate the presence or absence of a particular feature or attribute in a record.

The syntax for BIT data type: 

CREATE TABLE MyTable (   MyBITColumn BIT );

When the data is inserted into a BIT column, the user can either use 0 or 1 to represent false or true respectively, or can use the keywords false and true.

An example of BIT data type:

INSERT INTO customer (name, id, opt_in) VALUES (‘Santa Claus’, ‘santa.claus@bitexample.com’, 1);

The “opt_in” column is defined as a BIT field in the above example, while its value is defined to be true (1).

Example for Boolean Data Type

The value 1 represents “true” while the value 0 represents “false” when representing boolean parameters. For example, if a user wants to build a table that contains a column with boolean values, they can do so as instructed below:

CREATE TABLE table_name (     id INT PRIMARY KEY,     is_checked BIT(1) );

The user-defined a table called “table_name” with two fields- “id” and “is_checked” in the above example. The “is_checked” column is described as a BIT(1) SQL data type. It has the capability to store only one binary digit from 0 and 1. 

The user can then insert boolean values into this column as follows:

INSERT INTO table_name (id, is_checked) VALUES (1, 1); true INSERT INTO my_table (id, is_active) VALUES (2, 0); false

The user inserted two rows into the “table_name” table, with “is_checked” values of 1 and 0 in the example above. It represents “true” and “false” respectively.

SQL Decimal Data Type

Decimal SQL data types are used to describe fixed-point integers that have a defined scale and accuracy. The precision is the total number of digits that may be contained in the data type, whereas the scale is the number of digits to the right of the decimal point. It is typically used for data where precision is essential. 

A decimal column in a table is defined using the following syntax:

newDecimal DECIMAL(scale, precision)

The user would establish a column as follows if they wanted to store a value with a maximum of 10 digits and 2 digits after the decimal point:

newDecimal DECIMAL(5, 2)

Because the precision is capped at 5 digits, we can save figures like 145.86.

A decimal column in a table is defined using the following syntax:

newDecimal DECIMAL(scale, precision)

The user would establish a column as follows if they wanted to store a value with a maximum of 10 digits and 2 digits after the decimal point:

newDecimal DECIMAL(5, 2)

Because the precision is capped at 5 digits, we can save figures like 145.86.

SQL Float Data Type

FLOAT SQL data types represent a floating-point number. It stores values requiring a higher degree of precision and has a decimal point. Both, single-precision or double-precision floating-point numbers can be stored. 

The following syntax can be used to create a column with FLOAT:

CREATE TABLE my_table (   MyFloatColumn FLOAT );

The user can also specify the precision and scale of the FLOAT data type. For example:

CREATE TABLE my_table (   MyFloatColumn FLOAT(10,2) );

It would create a FLOAT column with 10 digits of precision and 2 digits after the decimal point.

Conclusion

In this blog, we have discussed various SQL data types including numeric, date/time, character, and binary data types. We have also seen how they play an essential role in defining the type of data that can be stored in a database table column. It is empirical to have a working knowledge of data types before working on SQL, so make sure you have familiarized yourself with all the data types.