SQL Data Types

SQL data types define the kind of values you can store in a database column. Choosing the correct data type is important because it affects storage, performance, and data accuracy. Every table column in SQL must have a data type assigned to it. 

Common SQL Data Types 

Numeric Data Types 

1. INT (Integer): Stores whole numbers without decimals. 
   Example: Ideal for storing quantities, counts, or IDs. 
2. SMALLINT / BIGINT: Variants of integer types for smaller or larger number ranges. 
   Example: SMALLINT for small counts (0–65,535), BIGINT for very large IDs or counters. 
3. FLOAT: Stores decimal numbers with floating precision. 
   Example: Useful for measurements, ratings, or percentages. 
4. DECIMAL / NUMERIC: Stores exact decimal values with fixed precision. 
   Example: Ideal for prices, salaries, or financial data to avoid rounding errors. 

String Data Types 

5. VARCHAR: Stores variable-length text strings. This means it only uses as much storage as the string length (plus 1 or 2 bytes for length information), rather than always taking the maximum size. The maximum length can be up to 65,535 characters, but the effective maximum depends on row size and character set.
   Example: VARCHAR (100) for names, email addresses, or titles which will have a range of 100 characters. 
6. CHAR: Stores fixed-length text strings. If the value is shorter than the defined length, it is padded with spaces to the right. The maximum length is 0 to 255 characters.
   Example: Country codes like CHAR (2) for "US", "PK". 
7. TEXT: Stores large amounts of text. 
   Example: Use TEXT for descriptions, articles, or notes. In SQL, there are four main text types:
   • TINYTEXT: up to 255 characters
   • TEXT: to 65,535 characters (~64 KB)
   • MEDIUMTEXT: up to 16,777,215 characters (~16 MB)
   • LONGTEXT: up to 4,294,967,295 characters (~4 GB)

Date and Time Data Types 

8. DATE: Stores date values in YYYY-MM-DD format. 
   Example: Birthdates, hire dates, or event dates. 
9. TIME: Stores only time in HH:MM:SS format. 
   Example: Store office opening or closing times. 
10. DATETIME / TIMESTAMP: Stores both date and time. 
    Example: Used for logging transactions or events. 
11. YEAR: Stores a year in four-digit format. 
    Example: Store the manufacturing year for a product. 

Boolean Type 

12. BOOLEAN: Stores TRUE or FALSE values. 
    Example: Use for fields like is_active or is_verified. 

Binary Data Types 

13. BLOB (Binary Large Object): Stores binary data like images, audio, or video files. 
    Example: Profile pictures, PDF documents. 
14. BINARY / VARBINARY: Stores fixed or variable length binary data. 
    Example: Storing encrypted passwords, hashes, or raw bytes. 

Special Data Types  

15. ENUM: ENUM allows you to store one value from a predefined set of strings. This restricts values and ensures data integrity. It can store up to 65,535 distinct values can be defined.
    Example: ENUM ('small', 'medium', 'large') for clothing sizes. 
16. ARRAY: ARRAY allows a single column to store multiple values of the same type (like an array in programming). It supports multidimensional arrays as well.
    Example: List of phone numbers in a single column. 
17. JSON / JSONB: JSON and JSONB store semi-structured data in JSON format.JSON keeps the text exactly as provided.JSONB stores a binary optimized format for faster querying. Example: Product specifications in e-commerce apps. Relation to NoSQL:
    • JSON in SQL databases works like a mini NoSQL document store.
    • Instead of designing strict schemas with columns, you can store dynamic data inside JSON.
    • This mimics how NoSQL databases (like MongoDB) store documents, but within a relational database engine.
    • Many developers use it when they want the flexibility of NoSQL while still benefiting from ACID properties, SQL queries, and joins.
18. UUID: UUID stores a 128-bit globally unique identifier, often represented as a 36-character string (8-4-4-4-12 format). It can store up to 2^128 possible values.
    Example: Secure unique IDs for distributed systems. 

Default Values and Auto Increment 

Default Values: You can assign a default value to a column so that if no data is provided, SQL automatically uses that value.  

status VARCHAR (20) DEFAULT 'Pending' 

AUTO_INCREMENT: Automatically generates a unique number for each new record. Commonly used for primary keys. 

id INT PRIMARY KEY AUTO_INCREMENT 

For a deeper dive and more learning about SQL data types, visit the official documentation: 

MySQL: https://dev.mysql.com/doc/refman/8.4/en/data-types.html

PostgreSQL: https://www.postgresql.org/docs/current/datatype.html

FAQs 

1. What is the difference between INT and BIGINT in SQL? 
INT is used for whole numbers within a standard range (commonly up to around 2 billion). BIGINT supports much larger numbers, making it useful for cases like very large counters, financial transactions, or global user IDs. 

2. What is the difference between CHAR and VARCHAR in SQL? 

• CHAR stores fixed-length strings and pads them with spaces if shorter. 
• VARCHAR stores variable-length strings and saves space by only using the needed length. 
  Use CHAR for things like country codes and VARCHAR for names or email addresses. 

3. Can we store images or files in SQL databases? 
Yes. Binary data types such as BLOB, BINARY, or VARBINARY are used to store images, videos, and documents. However, in practice, many systems prefer storing files in external storage (like AWS S3) and only keep file paths or metadata in the database. 

4. What is the difference between DATE, DATETIME, and TIMESTAMP in SQL? 

• DATE stores only the calendar date (YYYY-MM-DD). 
• DATETIME stores both date and time. 
• TIMESTAMP also stores date and time, but it includes time zone and is often used for logging system events. 

5. What is a UUID data type used for? 
The UUID (Universally Unique Identifier) data type is used to generate globally unique values that are very difficult to duplicate. It’s often used in distributed systems, user IDs, or where data must be uniquely identified across multiple databases.