Database Objects: Views, Indexes & More

Beyond tables

Common database objects

Views

A view is a saved SQL query that acts like a virtual table. It doesn’t store data — it runs the query every time you access it.

-- Create a view showing active drivers with their delivery count
CREATE VIEW ActiveDriverSummary AS
SELECT d.Name, d.LicenceClass, COUNT(del.DeliveryID) AS TotalDeliveries
FROM Drivers d
JOIN Deliveries del ON d.DriverID = del.DriverID
WHERE d.IsActive = 1
GROUP BY d.Name, d.LicenceClass;

-- Use the view like a table
SELECT * FROM ActiveDriverSummary;

Why use views?

• Simplify complex queries — write the join once, reuse it as a simple table name
• Security — show users specific columns without exposing the full table
• Consistency — everyone uses the same query logic

Indexes

An index is a data structure that speeds up data retrieval — like the index at the back of a textbook. Instead of reading every page to find a topic, you look it up in the index and go directly to the right page.

-- Create an index on the Deliveries table for faster date lookups
CREATE INDEX idx_delivery_date ON Deliveries (Date);

Key points:

• Indexes speed up SELECT queries dramatically
• They slow down INSERT/UPDATE/DELETE slightly (the index must be updated too)
• The database automatically creates an index on the primary key
• Add indexes on columns frequently used in WHERE and JOIN clauses

Stored procedures

A stored procedure is a saved block of SQL that you can call by name. Think of it as a reusable function.

-- Create a stored procedure
CREATE PROCEDURE AssignDriver
  @DeliveryID VARCHAR(10),
  @DriverID VARCHAR(10)
AS
BEGIN
  UPDATE Deliveries SET DriverID = @DriverID WHERE DeliveryID = @DeliveryID;
  UPDATE Drivers SET IsActive = 0 WHERE DriverID = @DriverID;
END;

-- Call it
EXEC AssignDriver @DeliveryID = 'DEL-504', @DriverID = 'D001';

Why use stored procedures?

• Encapsulate logic — complex operations in one callable unit
• Performance — precompiled and optimised by the database
• Security — users can run the procedure without direct table access
• Consistency — the same business logic every time

Functions

Functions are similar to stored procedures but return a value. They can be used inside SQL queries.

• Scalar functions — return a single value
• Table-valued functions — return a result set (like a view with parameters)

Views vs indexes vs stored procedures

Feature	View	Index	Stored Procedure
What it is	A saved query (virtual table)	A lookup structure for fast searches	A saved block of SQL logic
Stores data?	No — runs the query each time	Yes — a copy of indexed columns	No — stores code, not data
Main benefit	Simplify and secure queries	Speed up data retrieval	Encapsulate reusable logic
Example use	Show active drivers with stats	Find deliveries by date faster	Assign a driver to a delivery

💡 Exam tip: object identification

The exam describes a scenario and asks which database object to use:

• “Speed up queries that filter by date” → Index
• “Give analysts a simplified table without exposing raw data” → View
• “Automate a multi-step business process in the database” → Stored procedure
• “Reuse a calculation across multiple queries” → Function

Flashcards

Question

What is a database view?

Click or press Enter to reveal answer

Answer

A saved SQL query that acts like a virtual table. It doesn't store data — it runs the underlying query each time you access it. Used for simplification, security, and consistency.

Click to flip back

Question

What does a database index do?

Click or press Enter to reveal answer

Answer

An index speeds up data retrieval by creating a lookup structure on one or more columns — like a book index. It makes SELECT queries faster but adds slight overhead to INSERT/UPDATE/DELETE operations.

Click to flip back

Question

What is a stored procedure?

Click or press Enter to reveal answer

Answer

A precompiled, saved block of SQL that can be called by name. Used to encapsulate complex logic, improve performance, enforce security, and ensure consistent business operations.

Click to flip back

Knowledge check

Knowledge Check

Jake's CloudPulse database has a query that joins 5 tables to show customer usage reports. Multiple team members need to run this query daily. What should Jake create?

AAn index on each of the 5 tablesBA view that encapsulates the complex join queryCA stored procedure that runs the queryDA new table that duplicates the join result

Check Answer

Knowledge Check

Tom's Deliveries table has 2 million rows. Queries that filter by delivery date are taking 15 seconds. What should the DBA create to improve performance?

AA view on the Deliveries tableBA stored procedure for date queriesCAn index on the Date columnDA new table for recent deliveries only

Check Answer

Next up: Azure SQL: Your Database in the Cloud — let’s move from database concepts to real Azure services.