Which relational database object fits: index vs view vs stored procedure
Verdict: Choose an index to speed up searches on a column, a view to save a reusable SELECT query, or a stored procedure to package parameterised SQL that runs on command.
| Criterion | Index | View | Stored procedure |
|---|---|---|---|
| What it is | Sorted copy of a column with pointers to the matching rows | Virtual table based on the results of a SELECT query | SQL statements that can be run on command, optionally with parameters |
| Use when | Filtering or searching a large table on that column is slow | You want to save and reuse a query as if it were a table | You need to package a repeated, parameterised data action |
Rules
- An index stores a sorted copy of a column with pointers to the matching rows, so WHERE-clause queries against that column skip a full table scan.
- A view reorganises how data is presented as a virtual table built from a SELECT query; it does not add a sorted structure for faster lookups.
- A stored procedure defines SQL statements that run on command and can accept parameters, encapsulating logic for a common data action.
- A primary key enforces row uniqueness; it is not the object to add specifically to speed up filtering or to package runnable logic.
Traps
- Treating a view as something that speeds up searches: a view changes how data is presented, it does not provide a sorted lookup structure.
- Treating a stored procedure as a speed optimisation: it packages logic to run on demand but does not change how quickly rows are located.
- Treating an index as something that can accept parameters or run as a unit of logic: it only improves query speed.
- Treating a primary key as a container for runnable SQL: it is a uniqueness constraint on a column, not stored logic.