SQL Relational Databases: Tables

Tables

This section provides comprehensive documentation on tables within relational databases, focusing on Microsoft SQL Server.

Introduction to Tables

A table is the fundamental data structure for storing data in a relational database. It consists of rows (records) and columns (fields). Each column has a specific data type, and each row contains values for each column.

Creating Tables

You can create tables using the CREATE TABLE statement in SQL. This statement defines the table name, column names, their data types, and any constraints.

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()
);

Altering Tables

The ALTER TABLE statement is used to modify an existing table. You can add, delete, or modify columns, as well as add or drop constraints.

-- Add a new column
ALTER TABLE Customers
ADD Phone VARCHAR(20);

-- Modify an existing column's data type
ALTER TABLE Customers
ALTER COLUMN FirstName VARCHAR(75) NOT NULL;

-- Drop a column
ALTER TABLE Customers
DROP COLUMN Phone;

Dropping Tables

The DROP TABLE statement is used to delete a table and all its data. This action is irreversible.

DROP TABLE Customers;

Table Properties

Tables have various properties, including:

Schema: The logical grouping of database objects.
Filegroup: Where the table's data is stored on disk.
Partitioning: Dividing a table into smaller, more manageable parts.
Identity Columns: Columns that automatically generate unique sequential numbers.

Constraints

Constraints enforce data integrity. Common constraints include:

PRIMARY KEY: Uniquely identifies each row in a table.
FOREIGN KEY: Establishes a link between tables.
UNIQUE: Ensures all values in a column are different.
NOT NULL: Ensures a column cannot have a NULL value.
CHECK: Limits the range of values that can be placed in a column.
DEFAULT: Assigns a default value if no value is specified.

Indexing

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. Properly designed indexes are crucial for performance.

-- Create a non-clustered index on LastName
CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON Customers (LastName);

Learn more about advanced table operations and performance tuning in the Advanced Topics section.

MSDN Documentation