Open In App

SQL Data Types

Last Updated : 24 May, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

A column’s data type is essentially the type of data format that will be used to store the data in each cell; examples include any type of integer, character, money, date and time, binary, etc. We’ll acquire in-depth information about SQL Data Types in this tutorial.

SQL Data Types

An SQL developer must know what data type will be stored inside each column while creating a table. The data type guideline for SQL is to understand what type of data is expected inside each column and it also identifies how SQL will interact with the stored data.

For every database, data types are primarily classified into three categories.

  • Numeric Datatypes
  • Date and Time Datatypes
  • String Datatypes

Like in other programming languages, SQL also has certain datatypes available. A brief idea of all the datatypes is discussed below.

Numeric Data Types in MYSQL

Exact Numeric Datatype

There are nine subtypes which are given below in the table. The table contains the range of data in a particular type. 

Data Type

From 

To

BigInt

-263 (-9,223,372,036,854,775,808)

263 -1 (9,223,372,036,854,775,807)

Int

-231 (-2,147,483,648)

231-1 (2,147,483,647)

smallint

-215 (-32,768)

215-1 (32,767)

tinyint

0

28-1 (255)

bit

0

1

decimal

-1038+1

1038-1

numeric

-1038+1

1038-1

money 

-922,337,203,685,477.5808

922,337,203,685,477.5807

smallmoney

-214,748.3648

214,748.3647

Approximate Numeric Datatype

The subtypes of this datatype are given in the table with the range. 

Data Type From To

Float

-1.79E+308

1.79E+308

Real

-3.40E+38

3.40E+38

String Data Types in MYSQL

Character String Datatype

The subtypes are given in below table – 

Data Type Description

char

The maximum length of 8000 characters.(Fixed-Length non-Unicode Characters)

varchar

The maximum length of 8000 characters.(Variable-Length non-Unicode Characters)

varchar(max)

The maximum length of 231 characters(SQL Server 2005 only).(Variable Length non-Unicode data)

text

The maximum length of 2,127,483,647 characters(Variable Length non-Unicode data)

Unicode Character String Datatype

The details are given in below table – 

Data Type

Description

nchar

The maximum length of 4000 characters(Fixed-Length Unicode Characters)

Nvarchar

The maximum length of 4000 characters.(Variable-Length Unicode Characters)

nvarchar(max)

The maximum length of 231 characters(SQL Server 2005 only).(Variable Length Unicode data)

Server String Data Type in SQL

There are four subtypes of this datatype which are given below:

Datatypes

Description

Binary

The maximum length of 8000 bytes(Fixed-Length binary data)

varbinary

The maximum length of 8000 bytes(Variable Length binary data)

varbinary(max)

The maximum length of 231 bytes(SQL Server 2005 only).(Variable Length binary data)

text

Maximum Length of 2,147,483,647 bytes(Variable Length binary data)

Server Date and Time Data Type in SQL

The details are given in the below table.

Data Type Description

DATE

A data type is used to store the data of date in a record 

TIME

A data type is used to store the data of time in a record

DATETIME

A data type is used to store both the data,date, and time in the record.

Other Data Types

XML Datatype

XML data type allows storage of XML documents and fragments in a SQL Server database

DataType

Description

XML Datatype

A Datatype used to store data in the format of XML datatype

Spatial Dataype

A datatype is used for storing planar spatial data, such as points, lines, and polygons, in a database table.

DataType

Description

Geometry

A datatype is used for storing planar spatial data, such as points, lines, and polygons, in a database table.

Array Datatype

SQL Server does not have a built-in array datatype. However, it is possible to simulate arrays using tables or XML data types.

This text discusses the properties of data types in MYSQL.

SQL data types define the type of data that can be stored in a database column or variable. Here are the most common SQL data types:

Datatype

Properties

Numeric data types

These are used to store numeric values. Examples include INT, BIGINT, DECIMAL, and FLOAT.

Character data types

These are used to store character strings. Examples include CHAR, VARCHAR, and TEXT.

Date and time data types

These are used to store date and time values. Examples include DATE, TIME, and TIMESTAMP

Binary data types

These are used to store binary data, such as images or audio files. Examples include BLOB and BYTEA.

Boolean data type

This data type is used to store logical values. The only possible values are TRUE and FALSE.

Interval data types

These are used to store intervals of time. Examples include INTERVAL YEAR, INTERVAL MONTH, and INTERVAL DAY.

Array data types

These are used to store arrays of values. Examples include ARRAY and JSON.

XML data type

This data type is used to store XML data.

Spatial data types

These are used to store geometric or geographic data. Examples include POINT, LINE, and POLYGON.

Different databases may have different variations of these data types, or they may have additional data types not listed here. Understanding SQL data types are important for creating tables and working with data in a database, as it affects how data is stored and processed.



S
sakshijain1
News
Improve
Previous Article
What is SQL?
Next Article
SQL Operators

Similar Reads

How to Convert Data From SQL to C Data Types?
At the point when an application calls SQLExecute or SQLExecDirect, the driver recovers the data for any boundaries bound with SQLBindParameter from capacity areas in the application. At the point when an application calls SQLSetPos, the driver recovers the data for an update or adds activity from sections bound with SQLBindCol. For data-at-executi
6 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL vs NO SQL vs NEW SQL
SQL stands for Structured Query Language. Which is based on relational algebra and schema is fixed in this which means data is stored in the form of columns and tables. SQL follows ACID properties which means Atomicity, Consistency, Isolation, and Durability are maintained. There are three types of languages present in SQL : Data Definition Languag
2 min read
Numeric and Date-time data types in SQL Server
MS SQL Server supports a wide range of data types. There are a few more important data types that are included in the article. In this article, we will cover numeric SQL server data types and different date-time data types. Let’s discuss one by one. bit : A bit is the smallest unit of a computer system. A bit can be either 0 or 1. The bit datatype
3 min read
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter. A parameter whose value is given into a stored proced
3 min read
Difference between T-SQL and PL-SQL
1. Transact SQL (T-SQL) : T-SQL is an abbreviation for Transact Structure Query Language. It is a product by Microsoft and is an extension of SQL Language which is used to interact with relational databases. It is considered to perform best with Microsoft SQL servers. T-SQL statements are used to perform the transactions to the databases. T-SQL has
3 min read
SQL - SELECT from Multiple Tables with MS SQL Server
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n
3 min read
How to Execute SQL Server Stored Procedure in SQL Developer?
A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again. In this article let us see how to execute SQL Serv
2 min read
SQL Query to Check if Date is Greater Than Today in SQL
In this article, we will see the SQL query to check if DATE is greater than today's date by comparing date with today's date using the GETDATE() function. This function in SQL Server is used to return the present date and time of the database system in a ‘YYYY-MM-DD hh:mm: ss. mmm’ pattern. Features: This function is used to find the present date a
2 min read
SQL Query to Add a New Column After an Existing Column in SQL
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve data from relational databases like MySQL, Oracle, SQL Server, Postgres, etc. In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modif
3 min read
SQL Query to Convert Rows to Columns in SQL Server
In this article we will see, how to convert Rows to Column in SQL Server. In a table where many columns have the have same data for many entries in the table, it is advisable to convert the rows to column. This will help to reduce the table and make the table more readable. For example, Suppose we have a table given below: NAMECOLLEGEROLL NUMBERSUB
2 min read
How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then querying the outcomes. The idea of utilizing SQL
3 min read
SQL Quiz : Practice SQL Questions Online
This SQL quiz covers various topics like SQL basics, CRUD operations, operators, aggregation functions, constraints, joins, indexes, transactions, and query-based scenarios. We've included multiple-choice questions, fill-in-the-blank questions, and interactive coding challenges to keep things interesting and challenging. Whether you're a beginner l
3 min read
BULK INSERT in SQL Server(T-SQL command)
BULK INSERT in SQL Server(T-SQL command): In this article, we will cover bulk insert data from csv file using the T-SQL command in the SQL server and the way it is more useful and more convenient to perform such kind of operations. Let's discuss it one by one.  ConditionSometimes there is a scenario when we have to perform bulk insert data from .cs
3 min read
Difference between SQL and T-SQL
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, enabling operations like querying, updating, and deleting data. T-SQL (Transact-SQL), an extension of SQL developed by Microsoft, adds advanced features and procedural capabilities specifically for SQL Server. In this article, We will learn
4 min read
SQL Server | Convert Tables in T-SQL into XML
XML (Extensible Markup Language) is a widely-used markup language designed to store and transfer structured data between different systems and platforms. While HTML focuses on the visual representation of data OverviewXML is similar to HTML which is designed to structure and store data for sharing across different systems and platforms.Unlike HTML,
3 min read
SQL Exercises : SQL Practice with Solution for Beginners and Experienced
SQL (Structured Query Language) is a powerful tool used for managing and manipulating relational databases. Whether we are beginners or experienced professionals, practicing SQL exercises is important for improving your skills. Regular practice helps you get better at using SQL and boosts your confidence in handling different database tasks. So, in
15+ min read
SQL using Python | Set 3 (Handling large data)
It is recommended to go through SQL using Python | Set 1 and SQL using Python and SQLite | Set 2 In the previous articles the records of the database were limited to small size and single tuple. This article will explain how to write & fetch large data from the database using module SQLite3 covering all exceptions. A simple way is to execute th
4 min read
SQL | Checking Existing Constraints on a Table using Data Dictionaries
Prerequisite: SQL-Constraints In SQL Server the data dictionary is a set of database tables used to store information about a database's definition. One can use these data dictionaries to check the constraints on an already existing table and to change them(if possible). USER_CONSTRAINTS Data Dictionary: This data dictionary contains information ab
2 min read
How to Export Data From SQL Server to Flat File in SSIS?
In this article, we will learn how to Export Data From SQL Server To Flat File in SSIS. For this, we are going to create a package to select the data from the SQL server database and export SQL Server table data to the Flat File in the local drive. Prerequisites:Make sure Visual Studio 2019 is installed.Ensure that SQL Server Management Studio is i
3 min read
Manipulate R Data Frames Using SQL
Manipulating data frames in R Programming using SQL can be easily done using the sqldf package. This package in R provides a mechanism that allows data frame manipulation with SQL and also helps to connect with a limited number of databases. The sqldf package in R is basically used for executing the SQL commands or statements on data frames. One ca
8 min read
SELECT data from Multiple Tables in SQL
The statement is used to retrieve the fields from multiple tables and with the help of JOIN operations we easily fetch the records from multiple tables, Generally JOINS are used when there are common records between two tables. In this article, we will look into various types of JOIN that are used in SQL. SQL JoinsSQL joins combine two or more tabl
3 min read
Casting value or an expression from one data type to another in SQL server
SQL Server uses the CAST() function to cast or convert a value or an expression from one data type to another. Syntax : CAST ( value AS targettype [ ( length ) ] ) Parameters used : value - value can be any value of any type that will be converted. targettype - targettype is the target data type to which the value will be converted. e.g. INT, BIT,
2 min read
Create a User-Defined Data Type Alias in SQL Server
SQL Server has multiple data types e.g. integers, char, varchar, doubles, strings, etc. which are used for keeping specific values. Although built-in data types could store specific values, sometimes SQL DBA may need to store more specific values and create customized data types. We can create a user-defined data type using below two methods - Usin
2 min read
SQL Query to select Data from Tables Using Join and Where
The aim of this article is to make a simple program to Join two tables using Join and Where clause using MySQL. Below is the method to do the same using MySQL. The prerequisites of this article are MySQL and Apache Server on your computer are installed. What is a Query in SQL? A SQL query is a request passed for data/information from a table in a d
3 min read
Different Ways to Know Count of TempDB Data Files in SQL Server
The TempDB database is one of the most significant SQL Server system databases, and it's used to store temporary user objects like user-defined temporary tables, temporary stored procedures, table variables, and indexes, as well as transient stored procedures, table variables, and indexes. To allow transactions to be rolled back, operations in temp
3 min read
How to Append Data to a SQL Column?
In this Article we will update a record while keeping the existing record value if it exists. Whenever we need to just update the record after some time of creation the record We will use Append Query in SQL. For Implementing the Append Query in SQL Here First of all we will create Database and inside the we will create a table with some Record. St
2 min read
How to Group and Aggregate Data Using SQL?
There can be many columns in a database table, so sometimes it can become difficult and time taking to find the same type of data in these columns. The GROUP BY statement groups the identical rows present in the columns of a table. GROUP BY statement in conjunction with SQL aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG() etc.) help us to a
4 min read
How to Export SQL Server Data to a CSV File?
Here we will see, how to export SQL Server Data to CSV file by using the 'Import and Export wizard' of SQL Server Management Studio (SSMS). CSV (Comma-separated values): It is a file that consists of plain text data in which data is separated using comma(,). It is also known as Comma Delimited Files because comma act as a delimiter for CSV files. S
2 min read
Article Tags :
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox