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.