3.2 INDEX, VIEW, and FUNCTION

Tables are not the only database objects. Indexes, views, and functions can make queries faster, clearer, or move some logic into the database.

INDEX

An index helps the database find rows faster:

sql

CREATE INDEX idx_students_email
ON students (email);

If you often query with WHERE email = ?, an index on email may help.

But indexes are not free:

They need extra storage.
INSERT, UPDATE, and DELETE must maintain them.
Too many indexes can slow writes.
An index that does not match query patterns may not help.

Loading concept check...

VIEW

A view is a saved query definition:

sql

CREATE VIEW student_course_summary AS
SELECT s.student_id, s.name, c.title
FROM students AS s
JOIN courses AS c
  ON c.course_id = s.course_id;

Then you can query it like a table:

sql

SELECT *
FROM student_course_summary;

Views are useful for hiding complex JOINs, standardizing report definitions, and limiting exposed columns.

Note

Product note: A normal VIEW usually stores a query definition, not necessarily physical result rows. Materialized View is a different object supported by products such as PostgreSQL and Oracle. MySQL does not have the same built-in materialized-view syntax. SQL Server has indexed views with several restrictions.

FUNCTION

A database function can package calculation logic, such as turning a score into a grade. But function syntax is highly product-specific.

Note

Product note: PostgreSQL can define functions in SQL, PL/pgSQL, and other languages; MySQL has its own CREATE FUNCTION syntax; SQL Server uses T-SQL scalar or table-valued functions; Oracle uses PL/SQL. Do not treat one product's function syntax as portable SQL.

Loading interactive lab...

Loading concept check...

When to put logic in the database

Often good inside the database:

Data integrity constraints.
Simple rules shared by every application.
Calculations tightly coupled to queries.

Not always good inside the database:

Fast-changing business workflows.
Logic that calls complex external services.
Stored procedures that the team cannot easily test or maintain.

Loading practice...

3.2 INDEX, VIEW, and FUNCTION

Tables are not the only database objects. Indexes, views, and functions can make queries faster, clearer, or move some logic into the database.

INDEX

An index helps the database find rows faster:

sql

CREATE INDEX idx_students_email
ON students (email);

If you often query with WHERE email = ?, an index on email may help.

But indexes are not free:

They need extra storage.
INSERT, UPDATE, and DELETE must maintain them.
Too many indexes can slow writes.
An index that does not match query patterns may not help.

Loading concept check...

VIEW

A view is a saved query definition:

sql

CREATE VIEW student_course_summary AS
SELECT s.student_id, s.name, c.title
FROM students AS s
JOIN courses AS c
  ON c.course_id = s.course_id;

Then you can query it like a table:

sql

SELECT *
FROM student_course_summary;

Views are useful for hiding complex JOINs, standardizing report definitions, and limiting exposed columns.

Note

FUNCTION

A database function can package calculation logic, such as turning a score into a grade. But function syntax is highly product-specific.

Note

Loading interactive lab...

Loading concept check...

When to put logic in the database

Often good inside the database:

Data integrity constraints.
Simple rules shared by every application.
Calculations tightly coupled to queries.

Not always good inside the database:

Fast-changing business workflows.
Logic that calls complex external services.
Stored procedures that the team cannot easily test or maintain.

Loading practice...