SQL Data Types

SQL Data Types:

Data types define what kind of data can be stored in a database table’s columns. Choosing appropriate data types is crucial for efficient storage, accurate data manipulation, and effective queries.

Common SQL Data Types:

Numeric Types:

 

Integer Types:

INT:

It stores whole numbers from -2147483648 to 2147483647

Ex: employee ID, number of products

SMALLINT:

Stores smaller whole numbers from -32768 to 32767

Ex: Age

TINYINT:

It stores even smaller numbers from 0 to 255

Ex: flag for active/inactive

BIGINT:

It stores very large whole numbers

Ex: credit card numbers

Decimal Types:

DECIMAL:

It stores fixed-precision decimal numbers

 Ex:  prices, currency

NUMERIC:

 It similar to DECIMAL but it is used for precise numeric values.

FLOAT:

It stores approximate floating-point numbers

Ex: scientific calculations

Character and String Types:

Fixed-Length Types:

CHAR(n):

It stores fixed-length character strings

Ex:  state codes, product codes

Variable-Length Types:

VARCHAR(n):

It stores variable-length character strings

Ex:  names, addresses

TEXT:

It stores large text blocks

Ex: comments, descriptions

Unicode Types:

NCHAR(n): Stores fixed-length Unicode characters.

NVARCHAR(n): Stores variable-length Unicode characters.

NTEXT: Stores large Unicode text blocks.

 

Date and Time Types:

 

DATE:

It stores dates in YYYY-MM-DD format

Ex: birthdate, order date

TIME:

It stores time values in HH:MM:SS format

Ex:  meeting time

DATETIME:

It stores both date and time

Ex:  transaction timestamp

TIMESTAMP:

This is Similar to DATETIME, often used for auditing and version control.

Binary Types:

 

BINARY(n):

It stores fixed-length binary data

Ex:  images, files

VARBINARY(n): Stores variable-length binary data.

IMAGE: Stores large binary data (often deprecated in newer databases).

Boolean Type:

BOOLEAN:

It stores true or false values

 Ex:  is_active,  has_paid

Example:

CREATE TABLE Employee (

    EmpID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Email VARCHAR(100),

    DateOfBirth DATE,

    IsActive BOOLEAN

);

 

NOTE: Specific data types and their properties may vary slightly across different SQL database systems.

Join Our Newsletter