SQL Data Types Explained [Practical Examples]

What is Data type in SQL?

SQL Data types are attribute associated with a piece of data that tells a computer system how to interpret its value, A data type is an attribute that specifies the type of data that the object can hold. It’s important to understand data types because storing data in the appropriate format is fundamental to building usable databases and performing accurate analysis. In addition, a data type is a programming concept applicable to more than just SQL.

In a SQL database, each column in a table can hold one and only one data type, which is defined in the CREATE TABLE statement. You declare the data type after naming the column. Here’s a simple example that includes two columns, one a date and the other an integer:

CREATE TABLE eagle_watch (
    observed_date date,
    eagles_seen integer
);

In this table named eagle_watch (for an annual inventory of bald eagles), the observed_date column is declared to hold date values by adding the date type declaration after its name. Similarly, eagles_seen is set to hold whole numbers with the integer type declaration.

 

Overview of SQL Data types

In SQL each column, local variable, expression, and parameter has a related data type like integer data, character data, monetary data, date and time data, binary strings, and so on , SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. We can also define your own data types in Transact-SQL

SQL Data types Categories

1. Exact Numeric Data Types
2. Approximate Numeric Data Types
3. Character strings Data Types
4. Unicode character strings Data Types
5. Binary strings Data Types
6. Date and Time Data Types
7. Other Data Types
8. User-Defined Data Types

 

Exact Numeric Data Types

The exact numeric data types are used to store numeric values

 

Example of SQL Numeric Data Types

create table numeric_table(column1 Bigint , 
column2 Numeric ,
column3 Bit ,
column4 Smallint ,
column5 Decimal ,
column6 smallmoney ,
column7 Int ,
column8 tinyint ,
column9 money) ;

• In the above example, we have created a table named numeric_table with 9 columns of numeric data type
• column1 is of bigint data type allows to store value of integer number from  -9,223,372,036,854,775,808 to +9,223,372,036,854,775,808 ,
• column2 is of numeric data type which allows storing integer number value between -10^38 +1 to 10^38 -1,
• column3 is of type Bit which allows only two values 0 or 1,
• column4 is of type smallint which allows storing the value from -32,768 to 32,767,
• column5 is of type Decimal which allows the value of numbers between -10^38 +1 to 10^38 -1
•  column6 is of type smallmoney which allow storing value between -214,748.3648  and +214,748.3647
• column7 is of type int which allows storing integer number value between the range of -2,147,483,648 to        2,147,483,647
• column8 is of type tinyint which allow storing value between 0 to 255
• column9 is of type money which allow to store value between -922,337,203,685,477.5808 and      +922,337,203,685,477.5807

OUTPUT:

INSERT INTO numeric_table (column1, column2, column3, column4, column5, column6, column7, column8, column9)
VALUES (8223373, 10000, 1, 31000, 200.10, 200.500, 100000, 253, 203456)

 

Approximate Numeric Data Types

 

Example of Approximate Numeric data types

create table Approximate_numeric_table(column1 Float , 
column2 real );

• In the above example, we have created a table named Approximate_numeric_table with 2 columns of approximate numeric data type
• column1 is of float data type allows storing the value of integer number from  -1.79E+308 to 1.79E+308
• column2 is of real data type which allows storing integer number value between -3.40E+38 to 3.40E+38

OUTPUT:

INSERT INTO Approximate_numeric_table (column1, column2)
VALUES(6000.300, 34444.6000)

 

Character strings Data Types

 

Example of SQL Character strings data types

create table character_string_table(column1 char(20) , 
column2 varchar(20),column3 text);

• In the above example, we have created a table named character_string_table with 2 columns of character_string_table data type
• column1 is of a char data type with a size of  value that allows storing the value of numbers, letters, and special characters size value from 0 to 255
• column2 is of varchar data type which allows storing variable-length string values between  0 to 65535
• column3 is of text type which allows for storing character string value  up to 255 characters

OUTPUT:

INSERT INTO character_string_table(column1, column2, column3)
VALUES ('eeee', 'Hello', 'SQL Datatypes')

 

SQL Unicode character string data types

 

Example of SQL Unicode character string data types

create table unicode_string_table(column1 nchar , 
column2 nvarchar,column3 ntext);

• In the above example, we have created a table named unicode_string_table with 3 columns of character_string_table data type
• column1 is of a nchar data type with a size of value that allows storing the value of numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1
• column2 is of nvarchar data type which allows storing variable-length string values between  0 to 65535
• column3 is of ntext type which allows for storing character string values up to 255 characters

OUTPUT:

INSERT INTO unicode_string_table(column1, column2, column3)
VALUES ('ABCD', 'Hello', 'SQL Datatypes tutorial')

 

Binary strings Data Types

 

Example of SQL Binary strings data types

create table binary_string_table(column1 binary , 
column2 varbinary,column3 image);

• In the above example, we have created a table named binary_string_table with 3 columns of character_string_table data type
• column1 is of a binary data type with fixed-length string Fixed length with a maximum length of 8,000 bytes
• column2 is of varbinary data type which allows the variable length of 8,000 bytes
• column3 is of text type which allows for storing character string values up to 255 characters

OUTPUT:

INSERT INTO binary_string_table(column1, column2, column3)
VALUES (0x42, 00000, 'C:\Image.jpg')

 

Date and Time Data Types

 

Example of SQL date and time data types

create table date_time_table(column1 date , 
column2 datetimeoffset,column3 datetime2,column4 smalldatetime,column5 datetime,column6 time);

• In the above example, we have created a table named date_time_table with 6 columns of DateTime data type
• column1 is of a date type which supports the range is from '1000-01-01' to '9999-12-31'
• column2 is of datetimeoffset type which allows to combine with a time of a day based on a 24-hour clock like datetime2, and adds time zone awareness based on UTC characters
• column3 is of datetime2 type which allows combining with a time of day that is based on the 24-hour clock
• column4 is of smalldatetime type which Defines a date that is combined with a time of day
• column5 is of datetime type which defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock
• column6 is of time type which defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock

OUTPUT:

INSERT INTO date_time_table
(column1, column2, column3, column4, column5, column6)
VALUES ('02-02-1990', '12-10-25 12:32:10 +01:00', '2016-12-21 00:00:00.0000000', '1955-12-13 12:43:10', '2007-05-08 12:35:29.123', '12:34:54.1237')

 

Other Data Types

 

Example of SQL Other data types

User-Defined Data Types

SQL Supports two forms of user-defined data types,

1. Distinct types
2. Structured data types

 

Distinct types

• This type is used when two or more attributes is having the same type values like department name and employee name both are having string value, A good system should be able to detect such assignments or comparisons, to support such checks, SQL provides the notion of distinct types
• The Create type clause is used to define new types

For example: create type Dollars as numeric(12,2) final;

• This will create user-defined types Dollars to be a decimal number with a total of 12 digits, two of which are placed after the decimal point
• The newly created types can then be used to declare attribute as follow

create table department
(dept_no int,
dept_name varchar(20),
budget Dollars);

 

Structured data types

• Structured data types allow the creation of complex data types with nested record structures, arrays , and multisets
• For example:

create type publisher as (name varchar(20),
branch varchar(20));

• We can use this structure type data type to create table in SQL
• create table book_publish of publisher

 

Summary

In this article on SQL data types, we first give an introduction of what is data types, an overview of SQL data types, also define a list of SQL data types, we have explained each category of data types and described each category data type with the storage range value, at the end of the article we have to cover user-defined data types with examples

 

References

SQL Data Types