7. SQL Data Types

Every column in a SQL table must have a data type defined. Data types are essential because they tell the database exactly what kind of information to expect (numbers, text, dates, etc.), how much space to reserve, and what operations (like arithmetic or sorting) are valid. Choosing the correct data type is critical for storage efficiency, data integrity, and query performance.

Here is a breakdown of the most common and important SQL data type categories:

---

1.  Numeric Types

Numeric types are used for storing different kinds of numbers, distinguishing between integers and decimals.

Type	Description	Common Use Cases
INT / INTEGER	A standard whole number (no decimals). Most systems offer variations like SMALLINT or BIGINT for different ranges.	Primary Keys, counts, quantities.
DECIMAL / NUMERIC	Stores exact fractional (decimal) numbers. You specify the precision and scale (e.g., DECIMAL(8, 2) for 8 total digits, 2 after the decimal).	Currency, fixed-point arithmetic, financial calculations.
FLOAT / REAL	Stores approximate, floating-point numbers. Less precise than DECIMAL due to how computers store them.	Scientific measurements, non-financial ratios.

---

2. String (Text) Types

These types store characters, letters, and numbers. The choice here often comes down to storage efficiency, as discussed previously (CHAR vs. VARCHAR).

Type	Description	Common Use Cases
VARCHAR(n)	Variable-length string. Only occupies the space necessary for the data plus a small overhead. n defines the maximum length.	Names, addresses, most text descriptions.
CHAR(n)	Fixed-length string. Always occupies n characters of storage space, padding with spaces if the string is shorter.	Fixed-length codes (e.g., state abbreviations, 'US', 'CA').
TEXT	Used for very large strings of text, like full articles or long notes. Doesn't require a defined maximum length.	Blog content, comments, long descriptions.

---

3. Date and Time Types

These types ensure that time-based data is stored in a consistent, standardized format, making temporal queries (e.g., "sales last month") simple.

Type	Description	Common Use Cases
DATE	Stores the date only (year, month, day).	Birth dates, invoice dates, holidays.
TIME	Stores the time only (hour, minute, second).	Opening hours, event start times.
DATETIME / TIMESTAMP	Stores both the date and the time, often down to milliseconds. TIMESTAMP often tracks time zone or is relative to an epoch.	Record creation/update times, transaction timestamps.

---

4. 💡 Boolean and Specialized Types

These types handle specific data needs beyond standard numbers or text.

Type	Description	Common Use Cases
BOOLEAN / BOOL	Stores simple true/false values. Some databases use TINYINT (0 or 1) instead of a native boolean type.	Flags (e.g., IsActive, IsPaid).
JSON / JSONB	Used to store data in the popular JavaScript Object Notation (JSON) format, allowing for flexible, semi-structured data within a relational table.	Storing configuration settings, API responses, or complex nested user preferences.
ENUM	Allows a column to hold one and only one value from a predefined list of allowed string values (e.g., 'Small', 'Medium', 'Large').	Status codes, severity levels.