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:
- How will be value ‘Good Morning’ stored in Character(20) and Varchar2(20)?
Character(20) ---- ‘Good Morning‘
Varchar2(20) ----- ‘Good Morning’
- Example of Boolean value.
Return_value = ‘True’
- Example of Date : ‘1958-08-22’
- Example of time: ’11.00 A.M’
- Example of Numeric or Decimal : Decimal(4,2) = 1000.24
- Example of INT : 5400, -2500