04 - SQL Data Types

In a relational database, data is stored in row and column format (Table form).

Consider a table having data about employee details. There are different column types and hence different data is expected in each column. In the employee name, we cannot define it as column with numbers as text is expected, similarly for salary we cannot define as text.

Serial no

Employee ID

Employee Name

Salary

Department

1

1200

John

12000

Sales

2

1201

Michael

13200

Operations

3

1202

Paula

11000

Backoffice

Data type is the attribute of the data expected in the column.  Depending upon the SQL implementation (version) different data types are available. Whenever you create a column using the data type, the SQL Implementation program would allocate an appropriate amount of space to store the data. Therefore, you have to choose a type appropriately so as to ensure that there is no wastage of storage space.

In general below data types are used in SQL.

Data Type

Description

NUMERICS

Integer

Data type does not have any fractional part and precision* depends on the SQL Implementation.

Can hold number from (--2,147,483,648 to -2,147,483,647)

Smallint

This is similar to Integer, the precision is less than integer and this assigns less storage space.

Can hold number from (-32,768 to -32,767)

Bigint

This is similar to Integer, the precision is greater  than integer.

Integer, SmallInt and BigInt is to be used if the data expected is only  whole number.

Can hold number from (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)

Numeric(p,s)

This data type can have a fractional component in addition to its integer component. Here p is the precision and s is the scale(number of digits after decimal point).

Can hold number from (--10^38 +1 to 10^38 -1)

Decimal(p,s)

Decimal is similar to Numeric. Only difference is between the different implementations.

Can hold number from (-10^38 +1 to 10^38 -1)

Float(p)

Can hold number from (-1.79E + 308 to 1.79E + 308)

Real

This is single precision floating number.

Can hold number from (-3.40E + 38 to 3.40E + 38)

Double precision

This is double precision floating number.

BINARY

Binary

This will hold fixed length value maximum length of 8000 bytes.

Varbinary

This will hold variable length value, maximum length of 8000 bytes.

STRINGS

Character(x)

This defines the number of characters the column can hold. If the data in the field is less than the specified length then SQL will fill the remaining character spaces with blank.

Varchar2(x)

This defines the number of characters the column can hold. Even if the data entered is less than specified length then SQL will not pad the field with blanks.

Boolean

The data type consists values as True or False. If the Boolean value is compared to a NULL or unknown value then the result will be UNKNOWN value.

DATETIME

Date

Stores year, month and date values.

Time

Stores Hour, Minute and seconds value.

Timestamp

Stores Year, Month, date, Hour, Minute and Seconds values.

Interval

Interval is the difference between two datetime values.

COLLECTIONS

Array

Array is a collection type and allows one of the other types to have multiple values within a single field in table.

Multiset

Multiset is an unordered collection.

XML

XML

XML datatype has a tree structure and a root node can have multiple children, which in turn may have multiple children. (This is supported by SQL Server 2005 only)

*precision means the maximum number of digits the number can have.

Examples:

  1. How will be value ‘Good Morning’ stored in Character(20) and Varchar2(20)?

       Character(20) ---- ‘Good Morning‘

       Varchar2(20) ----- ‘Good Morning’

  1. Example of Boolean value.

Return_value = ‘True’

  1. Example of Date : ‘1958-08-22’
  2. Example of time: ’11.00 A.M’
  3. Example of Numeric or Decimal : Decimal(4,2) = 1000.24
  4. Example of INT : 5400, -2500

 

 

Like us on Facebook