Topic 3 of 5
Database objects
A relational database isn't just tables. Views simplify queries, stored procedures hold logic, and indexes speed up searches. The exam asks you to identify each object from its description, and to know that indexes have a cost.
Contents
Beyond tables
Besides tables, a relational database contains other structures that help organize data, encapsulate programmatic actions, and speed up access. The three on the exam are:
View
A window on the data
Stored procedure
Logic stored in the database
Index
Faster searches
Views
A view is a virtual table based on the results of a SELECT query: a window on specific rows in one or more tables.
CREATE VIEW Deliveries
AS
SELECT o.OrderNo, o.OrderDate,
c.FirstName, c.LastName, c.Address, c.City
FROM [Order] AS o JOIN Customer AS c
ON o.Customer = c.ID;
-- The view is queried and filtered just like a table
SELECT OrderNo, OrderDate, LastName, Address
FROM Deliveries
WHERE City = 'Seattle';The Deliveries view brings orders and customers together in a single object, so it's easy to find each order's delivery address without writing the JOIN every time.
Stored procedures
A stored procedure defines SQL statements that can be run on command. It's used to encapsulate, in the database, the logic of actions that applications need to perform on data.
CREATE PROCEDURE RenameProduct
@ProductID INT,
@NewName VARCHAR(20)
AS
UPDATE Product
SET Name = @NewName
WHERE ID = @ProductID;
-- Run the procedure with its parameters
EXEC RenameProduct 201, 'Spanner';- Parameters (@ProductID, @NewName) make it flexible: the same action works for any product.
- It runs with EXEC, passing the values.
- In T-SQL, stored procedures and triggers are part of the dialect's proprietary extensions.
Indexes
An index helps you search for data in a table. It's like the index at the back of a book: a sorted list of references with the page where each one appears. Without it, you'd have to read the whole book.
Create the index: You specify a column; the index keeps a sorted copy of that data with pointers to the table rows.
Tree structure: The index forms a tree-based structure the query optimizer uses to find rows quickly.
Query: When a query filters on that column in the WHERE clause, the system uses the index instead of scanning the table row by row.
CREATE INDEX idx_ProductName
ON Product(Name);The cost of indexes
Benefits
On tables with many rows, indexes can dramatically improve query performance. You can create several per table: for example, another on Price to search by price.
Costs
They consume storage, and every INSERT, UPDATE, or DELETE has to maintain the table's indexes, which slows those operations down.
Small tables
On a table with few rows, using the index probably isn't more efficient than reading the whole table, and the optimizer ignores it. You have to balance faster queries against the cost of other operations.
Comparing objects
| Object | What it is | What for | Created with |
|---|---|---|---|
| Table | Rows and columns for an entity | Store the data | CREATE TABLE |
| View | A virtual table over a SELECT | Simplify queries, show a subset | CREATE VIEW |
| Stored procedure | SQL statements with parameters | Encapsulate reusable logic | CREATE PROCEDURE |
| Index | A sorted copy with pointers | Speed up searches | CREATE INDEX |
All of them are created with DDL statements (CREATE) and removed with DROP.
Exam scenarios
| Need | Object | Why |
|---|---|---|
| Give users a single object with orders and delivery addresses | View | A virtual table over a SELECT with a JOIN. |
| Encapsulate the logic to rename a product by its ID | Stored procedure | SQL statements with parameters, run on command. |
| Speed up product searches by name in a huge table | Index | A sorted copy with pointers to the rows. |
| A 20-row table with slow queries for some other reason | An index probably won't help | With few rows, reading the table is just as fast. |
| A table with a very high number of inserts per second | Be careful adding many indexes | Every change must maintain them. |
| Filter a view by city as if it were a table | Query the view with WHERE | Views are queried like tables. |
Common exam traps
❓ Does a view store a copy of the data?
No. It's a virtual table based on the results of a SELECT over the underlying tables.
❓ Can you filter a view with WHERE?
Yes. It's queried and filtered just like a table.
❓ Do more indexes always mean better performance?
No. They speed up queries, but take up space and slow down INSERT, UPDATE, and DELETE.
❓ Does an index always help, even if the table has few rows?
No. With few rows the optimizer usually ignores it because reading the table is just as efficient.
❓ Can a stored procedure take parameters?
Yes. Parameters let you reuse the same logic with different values.
❓ Which object is like the index of a book?
The index: sorted references with pointers to the rows.
Did you get this topic?
Test what you just learned
Users need to query orders along with the customer's address without writing a JOIN query every time. Which object should the administrator create?
Sign in to save your progress.