SQL Data Storage Concepts

Tables and Columns

The fundamental building blocks of a relational database are tables. A table is a collection of related data entries organized in a structured format. Each table consists of rows (also called records or tuples) and columns (also called fields or attributes).

Each column represents a specific type of data (e.g., customer name, product price, order date), and each row represents a single instance of that data (e.g., a specific customer, a particular product, a unique order).

Defining Tables

You use the CREATE TABLE statement to define the structure of a new table. This includes specifying the table name, column names, and the data type for each column.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE DEFAULT GETDATE()
);
        

Data Types

SQL databases support a wide range of data types to store different kinds of information efficiently. Choosing the correct data type is crucial for data integrity, storage efficiency, and performance.

Common data types include:

• Numeric: INT, DECIMAL, FLOAT
• String: VARCHAR, CHAR, TEXT
• Date and Time: DATE, TIME, DATETIME, TIMESTAMP
• Boolean: BIT (or equivalent)
• Binary: BLOB, VARBINARY

Primary Keys and Foreign Keys

These constraints are essential for maintaining relationships between tables and ensuring data integrity.

Primary Key

A primary key is a column or a set of columns that uniquely identifies each row in a table. It enforces entity integrity, meaning no two rows can have the same primary key value, and the primary key cannot contain NULL values.

Foreign Key

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a link between the two tables and enforces referential integrity, ensuring that relationships between tables are consistent.

Example of a foreign key constraint:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
        

Indexing

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. While not storing data itself, indexes store a sorted copy of data from one or more columns of a table, allowing the database to find rows more quickly without scanning the entire table.

Common types of indexes include B-tree indexes, hash indexes, and full-text indexes.

Creating an index:

CREATE INDEX idx_customer_lastname ON Customers (LastName);
        

Views

Views are virtual tables based on the result-set of a SQL statement. They do not store data themselves but provide a way to present data from one or more tables in a customized way. Views can simplify complex queries, restrict access to certain columns or rows, and provide a consistent interface even if the underlying table structure changes.

Creating a view:

CREATE VIEW CustomerOrderSummary AS
SELECT
    c.FirstName,
    c.LastName,
    COUNT(o.OrderID) AS TotalOrders
FROM
    Customers c
LEFT JOIN
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY
    c.FirstName, c.LastName;
        

Physical Storage

While this documentation focuses on the logical structure, it's important to note that databases store data physically on disk. The database management system (DBMS) handles the complex details of mapping logical structures (tables, rows) to physical storage (files, pages). Factors like disk I/O, memory caching, and storage engines significantly impact performance.