Data types in SQL with example

In this post you will be told how many types of data types are there in SQL,

Let us go into detail, so let’s start.

When you create a new relation (Table), you specify a data type for each of its columns/attributes. Similarly, when you create a new procedure, you specify a data type for each of its arguments. The data type defines the allowed values that each column or argument can store. For example, a DATE column cannot store a value of March 32, because this is not a valid date.

data-types-in-sql

Data types in SQL:-

SQL has different data types such as, Numeric, Characters, Binary, Date and Time.

You must know about data types before creating any tables. Data types have a huge role when creating tables. Data types you apply to fields (columns). Each column in a table stores a different type of value.

Data types are used to define which column will store what kind of value. The data type for each column is defined only when creating the table.

Data types in SQL are of the following types, so let’s know them.

Numeric Data Types: –

These data types are used to store numeric values. Numeric types are divided into 4 categories. These are being given below.

Data Type     Syntex    Range
  Integer    INT-2,147,483,648 to +2,147,483,648 
  Small Integer    SMALLINT-32,768 to +32,768 
  Big Integer    BIGINT-9,223,372,036,84,808 to +9,223,372,036,84,808
Tiny Integer  TINYINT 0 to 225 
Bit  Bit 0 to 1 
Numeric  NUMERIC-10^38+1 to +10^38+1 
Decimal  DECIMAL-10^38+1 to +10^38+1 
Numeric Data Types

Character Data Types:-

So Character string data types represent alphanumeric values (number and character). A character string is a very valuable data type, many programming languages use it.

Data Type    Syntex    Range
  Character String    CHARMaximum 8,000 Characters
  Character String    VARCHARMaximum 8,000 Characters
Character StringVARCHAR21 to 4000 bytes
Unicode Character String    NCHARMaximum 4,000 Characters
Unicode Character String  NVARCHARMaximum 4,000 Characters
Character String  TEXTMaximum 231 = 2,147,483,647 Characters
Unicode Character String  NTEXTMaximum 230 = 1,073,741,823 Characters
Character Data Types

Binary Data Types:-

Binary columns can contain data such as graphic images, which cannot easily be stored using character or numeric data types.

   Data Type      Syntex        Range
Binary Value BINARYMaximum 8,000 Bytes
Binary Value VARBINARYMaximum 8,000 Bytes
Image IMAGEMaximum 231 = 2,147,483,647 Bytes
Binary Data Types

Date and Time Data Types:-

Date and Time types in SQL are called Temporal types. In such data types, you can store temporal information like time, year, date etc. Temporal data types are like this.

Data Type Syntex        Range
 DateDATEFor Example, Feb 27, 2015(Format : YYYY-MM-DD)
 TimeTIMEFor Example, 2:15 A.M.(format : )
 DateTimeDATETIMERange : January 1, 1753, to December 31, 9999(format : YYYY-MM-DD HH:MI: SS)
SmallDateTimeSMALLDATETIMERange : January 1, 1900, to June 6, 2079(format: YYYY-MM-DD HH:MI: SS)
 TimestampTIMESTAMPFormat: YYYY-MM-DD HH:MI: SS (store year, month, day, hour, minute and second)
Date and Time Data Types

How did you like this post, and if you have any questions, tell us in the comment. And share it with your friends so that they too can get this information.

you can also read the article on RDBMS

for more technical article click on the link

Leave a Reply

Your email address will not be published. Required fields are marked *