MSDN Documentation

Tables and Views

This section covers the fundamental building blocks of relational databases in SQL Server: tables and views. Understanding how to define, manipulate, and query these objects is crucial for effective database development.

Tables

A table is a fundamental data structure in a relational database that organizes data into rows and columns. Each row represents a record, and each column represents an attribute of that record.

Creating Tables

The CREATE TABLE statement is used to define a new table. You specify the table name, column names, their data types, and constraints.

-- Example of creating a simple 'Customers' table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATETIME DEFAULT GETDATE()
);

Modifying Tables

The ALTER TABLE statement allows you to add, delete, or modify columns, as well as add or drop constraints.

-- Adding a new column to the Customers table
ALTER TABLE Customers
ADD PhoneNumber VARCHAR(20);

-- Dropping a column from the Customers table
ALTER TABLE Customers
DROP COLUMN PhoneNumber;

Deleting Tables

Use the DROP TABLE statement to remove a table and all its data.

-- Deleting the Customers table
DROP TABLE Customers;

Views

A view is a virtual table based on the result-set of a stored SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Creating Views

The CREATE VIEW statement defines a view.

-- Example of creating a view that shows customer names and email addresses
CREATE VIEW CustomerContactInfo AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    Email
FROM Customers
WHERE RegistrationDate > '2023-01-01';

Querying Views

You can query views just like you would query tables.

-- Selecting data from the CustomerContactInfo view
SELECT FirstName, LastName
FROM CustomerContactInfo
ORDER BY LastName;

Modifying Views

Use ALTER VIEW to modify an existing view.

-- Modifying the CustomerContactInfo view to include phone number (if it exists in Customers)
ALTER VIEW CustomerContactInfo AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    Email,
    PhoneNumber
FROM Customers;

Deleting Views

Use DROP VIEW to remove a view.

-- Deleting the CustomerContactInfo view
DROP VIEW CustomerContactInfo;

When to Use Tables vs. Views

• Tables: Use for storing the primary source of your data. They are the physical storage units.
• Views: Use to simplify complex queries, restrict data access (show only certain columns/rows), or present data in a specific format without duplicating it.

Common Table Expressions (CTEs)

CTEs provide a temporary, named result set that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). They are often used for recursive queries or to break down complex logic.

-- Example of a CTE to find customers registered in the last 30 days
WITH RecentCustomers (CustomerID, FirstName, LastName, RegDate)
AS
(
    SELECT
        CustomerID,
        FirstName,
        LastName,
        RegistrationDate
    FROM
        Customers
    WHERE
        RegistrationDate >= DATEADD(day, -30, GETDATE())
)
SELECT FirstName, LastName, RegDate
FROM RecentCustomers;