Views
What is a View?
A View is a virtual table based on the result of an SQL query. It doesn’t store data physically but presents data from one or more tables through a saved query.
Think of it as a window into a table—you can look at specific data without giving access to the entire table.
Syntax:
Now, you can simply run:
Benefits of Using Views:
- Simplicity: Hide complex queries from end users.
- Security: Limit access to sensitive columns.
- Reusability: Use the same query logic in multiple places.
- Data abstraction: Allow changes in table structure without affecting end users.
Limitations:
- Some views cannot be updated (especially when involving joins, aggregate functions).
- Views may become outdated if the underlying table changes drastically.
Indexes
What is an Index?
An Index is a data structure that improves the speed of data retrieval from a table, just like an index in a book helps you quickly locate topics.
Without indexes, SQL must scan the entire table to find matching rows (called a full table scan).
Syntax:
This index will speed up queries that filter or sort by name.
Types of Indexes:
- Single-column index: Built on one column.
- Composite index: Built on two or more columns.
- Unique index: Ensures no duplicate values (often used with primary keys).
- Clustered index: Determines the physical order of data (used by default in some DBMS like SQL Server).
Benefits of Using Indexes:
- Faster
SELECT,WHERE,ORDER BYoperations. - Improved query performance on large datasets.
Drawbacks:
- Slows down
INSERT,UPDATE, andDELETEoperations due to index updates. - Consumes additional disk space.
When to Use Views and Indexes
| Use Views When | Use Indexes When |
|---|---|
| You want to simplify complex queries | You need to speed up read-heavy queries |
| You need to restrict access to sensitive data | You frequently filter or sort by certain columns |
| You want logical separation from physical structure | Your table grows large and queries slow down |
