Other related articles:

Recently viewed articles:

SQL Datatypes

When we define a column in any table, we need to choose a data type for that column. Make the right choice of data type according to the use of  a column. Wrong choice of data type can occupy extra space in the database, or make the application program slow down by taking extra steps to convert the data into a more usable form.

Most commonly used  data types in  SQL are as follow:

  • CHAR(size),
  • DATE,
  • NUMBER,
  • LONG VARBINARY(BLOB),
  • LONG
  • VARCHAR(CLOB),
  • VARCHAR(size),
  • VARCHAR2

CHAR(size)

Character datatype stores text data/characters. A character can be any letter, number, or symbol. User has to specify in advance how many character are to be stored in column with character datatype. If the value for charater datatype is too big, it will allow RDBMS to pad extra space to each value which leads to wastage of space. One byte is allocated for each character. For example, if user specify char (8), it allocates 8 characters for the value. To store each value 8 bytes will be used in memory independent of length of string.

 VARCHAR(size)/VARCHAR2(size)

VARCHAR stands for VARiable CHARacter. It is very much similar to CHAR, except that the length of characters is variable. It uses memory depending on the size of the string entered. But the maximum size of the string has to be specified in advance during definition. It also stores one character per byte. VARCHAR2 is just the new version of VARCHAR which is now used in most of the companies.

FOR EXAMPLE

VARCHAR(8) will allocate space for 8 characters. But if the size of string is 2 characters, it will consume only 2 bytes of memory instead of 8 bytes as in CHAR datatype

DATE

It stores date value. There are many formats to store date value. The very commonly used is MM/DD/YYYY, where mm takes 2 digit value for month, DD takes 2 digit value for date and YYYY take 4 digit value for year.

There are number of different types of date formats available in any RDBMS.

’06/14/2007′

 NUMBER (length, precision)

NUMBER datatype stores only numeric values with or without decimal space. It stores one byte per digit. The length specifies total length of digits which can be stored. Precision is the number of digits after decimal.

For example

NUMBER (8,2) will allocate 8 bytes in memory out of which 2 digit are after decimal place. So the maximum value would be like 999999.99

These are some of the very commonly used datatypes in SQL and there name changes from one RDBMS package to another as described in image below: datatype