SQL Data Types Explained [Practical Examples]


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

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

Datatype Description
Bigint It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
Numeric It allows numbers between -10^38 +1 to 10^38 -1
Bit It allows two digits 0 and 1
Smallint It allows numbers from -32,768 to 32,767
Decimal It allows numbers between -10^38 +1 to 10^38 -1
smallmoney It allows numbers between -214,748.3648 and +214,748.3647
Int It allows numbers from -2,147,483,648 to 2,147,483,647
tinyint It allows values between 0 to 255
money It allows numbers between -922,337,203,685,477.5808 and +922,337,203,685,477.5807

 

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

Datatype Description
Float It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53
Real It is a floating precision number data from -3.40E+38 to 3.40E+38

c

 

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)

SQL Data Types Explained [Practical Examples]

 

Character strings Data Types

Datatype Description
Char(size) It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1
Varchar(size) It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters
Text(size) It holds a string that can contain a maximum length of 255 characters

 

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 Data Types Explained [Practical Examples]

 

SQL Unicode character string data types

Datatype Description
nchar It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1
nvarchar It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters
ntext It holds a string that can contain a maximum length of 255 characters

 

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')

SQL Data Types Explained [Practical Examples]

 

Binary strings Data Types

Datatype Description
binary It is used to specify a fixed length string Fixed length with a maximum length of 8,000 bytes
varbinary It is used to storage a variable length of 8,000 bytes
Image It is used to store the maximum length of 2,147,483,647 bytes

 

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

Datatype Description
date It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-01' to '9999-12-31'
datetimeoffset Defines a date that is combined with a time of a day based on a 24-hour clock like datetime2, and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time)
datetime2 Defines a date that is combined with a time of day that is based on 24-hour clock
smalldatetime Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds
datetime Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock
time Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock

 

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')

SQL Data Types Explained [Practical Examples]

 

Other Data Types

Datatype Description
cursor A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor
rowversion Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows.
The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type
hierarchyid The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree
uniqueidentifier It stores GUID (Globally unique identifier)
sql_variant A data type that stores values of various SQL Server-supported data types
Xml Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type
Spatial Geometry Types The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system
Spatial Geography Types The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates
table Is a special data type used to store a result set for processing at a later time. table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. Functions and variables can be declared to be of type table

 

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

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X