profile_img

SQL Data Types: A Clear and Concise Guide

Data types define the kind of information that each column in your database can hold. Choosing the right data type is crucial for any database, as it directly impacts the integrity, efficiency, and performance of your stored information. By defining data types, you establish clear rules for your database, ensuring that only compatible and accurate information finds its way into each column.

This guide delves into common data types used in SQL, providing clear explanations, code examples, and considerations for selection.

1. Numeric Data Types

Integer (INT):

  • Stores whole numbers without decimals
  • ideal for quantities, IDs, or ages.
  • Example: INT can hold values like 100, -5, or 0.
SQL 
                        
CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    age INT

);



INSERT INTO customers (customer_id, age) VALUES (1, 35);

Smallint:

  • Similar to INT but with a smaller range (-32,768 to 32,767).
  • Use it for space optimization when whole numbers fall within this range.
SQL 
                        
CREATE TABLE employees (

    employee_id SMALLINT PRIMARY KEY,

    age SMALLINT

);



INSERT INTO employees (employee_id, age) VALUES (1234, 35);

Bigint:

  • Stores larger whole numbers (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807).
  • Choose this for very large quantities or IDs.
SQL 
                        
CREATE TABLE products (

    product_id BIGINT PRIMARY KEY,

    stock_count BIGINT

);



INSERT INTO products (product_id, stock_count) VALUES (101, 9999999999);

Decimal:

  • Holds numbers with decimal precision
  • suitable for currency amounts, measurements, or calculations.
  • Example: DECIMAL(10,2) allows up to 10 digits (including the decimal point) with 2 decimal places.
SQL 
                        
CREATE TABLE products (

    product_id INT PRIMARY KEY,

    price DECIMAL(10,2)

);



INSERT INTO products (product_id, price) VALUES (101, 19.99);

Float:

  • Represents real numbers with single-precision floating-point accuracy
  • suitable for scientific data or engineering calculations.
  • Example: FLOAT can hold values like 3.14159 or -1.23456.
SQL 
                        
CREATE TABLE measurements (

    measurement_id INT PRIMARY KEY,

    value FLOAT

);



INSERT INTO measurements (measurement_id, value) VALUES (1, 3.14159);

2. String Data Types:

Char(n):

  • Fixed-length character string
  • useful for short, specific data like product codes or abbreviations.
  • Example: CHAR(5) can hold a maximum of 5 characters.
SQL 
                        
CREATE TABLE countries (

    country_code CHAR(2) PRIMARY KEY,

    name VARCHAR(50)

);



INSERT INTO countries (country_code, name) VALUES ('US', 'United States');

Varchar(n):

  • Variable-length character string
  • more efficient for storing text of varying lengths like names, addresses, or descriptions.
  • Example: VARCHAR(255) can hold up to 255 characters.
SQL 
                        
CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(25),

    last_name VARCHAR(30)

);



INSERT INTO employees (employee_id, first_name, last_name) VALUES (1234, 'John', 'Doe');

Text:

  • Used for very large text content, like blog posts or articles.
  • Choose this when storage space is not a major concern.
SQL 
                        
CREATE TABLE articles (

    article_id INT PRIMARY KEY,

    title VARCHAR(255),

    content TEXT

);



INSERT INTO articles (article_id, title, content) VALUES (

    1,

    'Demystifying Data Types: A Guide for Beginners',

    'This article provides a clear and concise introduction to common data types used in SQL...'

);

3. Date and Time Data Types:

Date:

  • Stores date information without time
  • suitable for recording birthdays, anniversaries, or order dates.
  • Example: DATE can hold values like 2024-01-29.
SQL 
                        
CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    order_date DATE

);



INSERT INTO orders (order_id, order_date) VALUES (555, '2024-01-29');

Time:

  • Stores time information without date
  • useful for recording timestamps or event durations.
  • Example: TIME can hold values like 12:34:56.
SQL 
                        
CREATE TABLE events (

    event_id INT PRIMARY KEY,

    start_time TIME

);



INSERT INTO events (event_id, start_time) VALUES (100, '12:34:56');

Datetime:

  • Combines date and time information
  • ideal for tracking events with exact timestamps.
  • Example: DATETIME can hold values like 2024-01-29 12:34:56.
SQL 
                        
CREATE TABLE events (

    event_id INT PRIMARY KEY,

    event_timestamp DATETIME

);



INSERT INTO events (event_id, event_timestamp) VALUES (100, '2024-01-29 12:34:56');

4. Boolean:

  • Stores true/false values (1/0 internally).
  • Useful for flags, status indicators, or binary choices.
  • Example: BOOLEAN can hold values like TRUE or FALSE.
SQL 
                        
CREATE TABLE users (

    user_id INT PRIMARY KEY,

    is_active BOOLEAN

);



INSERT INTO users (user_id, is_active) VALUES (1, TRUE);

5. Timestamp:

  • Similar to DATETIME but with additional precision for microseconds.
  • Use this for applications requiring highly accurate time tracking.
  • Example: TIMESTAMP can hold values like 2024-01-29 12:34:56.123456.
SQL 
                        
CREATE TABLE transactions (

    transaction_id INT PRIMARY KEY,

    transaction_time TIMESTAMP

);



INSERT INTO transactions (transaction_id, transaction_time) VALUES (222, '2024-01-29 12:34:56.123456');

6. Interval:

  • Represents a duration between two points in time.
  • Useful for storing time differences or calculating elapsed time.
  • Example: INTERVAL 10 DAY represents a duration of 10 days.
SQL 
                        
CREATE TABLE subscriptions (

    subscription_id INT PRIMARY KEY,

    duration INTERVAL

);



INSERT INTO subscriptions (subscription_id, duration) VALUES (10, INTERVAL '1 YEAR');

7. Geometry and Geography:

  • Specialized data types for storing and manipulating spatial data like points, lines, or polygons.
  • Use these for representing geographical locations, mapping applications, or spatial analysis.
  • Example: GEOMETRY and GEOGRAPHY offer different storage and processing methods for spatial data.
SQL 
                        
CREATE TABLE stores (

    store_id INT PRIMARY KEY,

    location GEOMETRY

);



INSERT INTO stores (store_id, location) VALUES (1, ST_GeomFromText('POINT(-122.4321 37.7789)'));

8. JSON:

  • Stores semi-structured data in JSON format, allowing flexibility for storing complex information.
  • Use this for data that doesn't fit neatly into traditional data types or has varying structures.
  • Example: JSON can hold nested objects, arrays, and key-value pairs.
SQL 
                        
CREATE TABLE product_details (

    product_id INT PRIMARY KEY,

    specs JSON

);



INSERT INTO product_details (product_id, specs) VALUES (101, '{"color": "red", "size": "M"}');

9. Array:

  • Stores an ordered collection of elements of the same data type.
  • Use this for representing lists, sets, or multiple values within a single column.
  • Example: INT ARRAY can hold multiple integer values like [1, 2, 3].
SQL 
                        
CREATE TABLE tags (

    post_id INT,

    tags INT ARRAY

);



INSERT INTO tags (post_id, tags) VALUES (50, '{1, 4, 7}');

10. Custom Data Types (User-Defined Types):

  • Allow you to define your own data types based on specific needs.
  • Use this for complex data structures or domain-specific data representation.
  • Example: You can create a custom type for "Address" containing street, city, and state fields.

Example in PostgreSQL

SQL 
                        
CREATE TYPE address AS (

    street VARCHAR(50),

    city VARCHAR(30),

    state CHAR(2),

    postal_code INT

);



CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    name VARCHAR(50),

    address address

);



INSERT INTO customers (customer_id, name, address) VALUES (1, 'John Doe', (

    '123 Main St', 'Anytown', 'CA', 90210

));

Example in MySQL

SQL 
                        
CREATE FUNCTION address_type(

  street VARCHAR(50),

  city VARCHAR(30),

  state CHAR(2),

  postal_code INT

) RETURNS JSON AS

BEGIN

  RETURN JSON_CREATE(

    {

      street: street,

      city: city,

      state: state,

      postal_code: postal_code

    }

  );

END;



CREATE TABLE customers (

  customer_id INT PRIMARY KEY,

  name VARCHAR(50),

  address address_type

);



INSERT INTO customers (customer_id, name, address) VALUES (1, 'Jane Doe', address_type(

  '456 Maple Ave', 'Anytown', 'NY', 10001

));

Note that creating and using custom data types might involve specific syntax and functionalities depending on your chosen database system. Always refer to the official documentation for detailed instructions and examples relevant to your specific environment.

Choosing the Right Data Type:

Selecting the appropriate data type is crucial for optimal performance and data integrity. Consider these factors:

  • Data characteristics: Understand the nature and range of values you'll be storing.
  • Storage efficiency: Choose data types that minimize storage space without compromising accuracy.
  • Operations: Ensure the data type supports the calculations or comparisons you need to perform.
  • Standardization: Consider using consistent data types for similar data across your tables.

Additional considerations

  • Not all database systems support all data types. Check your specific system's documentation for availability and specific implementation details.
  • Choosing the right data type involves careful consideration of data characteristics, storage efficiency, and operational needs.
© 2024 Asaqeni Learning
Tech Skills for Impact