Tables, Views, Indexes, Procedures: A Map of Every SQL Server Database Object
A database is more than tables. Here’s everything you’ll eventually build, with enough detail on what each one actually is and why it exists — not just a name to recognize — so later lessons have a real place to click into your mental map instead of feeling like brand-new territory.
The Full Picture
What Each One Actually Is
| Object | What it is | Why it exists |
|---|---|---|
| Table | The physical storage of rows and columns | Everything else in this list either reads from, protects, or accelerates access to tables |
| View | A saved, named SELECT query that behaves like a virtual table | Hides complex JOIN logic behind a simple name; lets you expose a restricted subset of columns without granting access to the base table |
| Index | An auxiliary sorted structure pointing back to table rows (conceptually, a book’s index) | Turns “scan every row to find this one” into “jump almost directly to it” — the difference between milliseconds and minutes on a large table |
| Constraint | A rule attached to a table that SQL Server enforces on every INSERT/UPDATE (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, DEFAULT) | Makes bad data structurally impossible to insert, rather than trusting every application to validate correctly |
| Stored Procedure | A named, reusable, parameterized block of T-SQL, saved inside the database | Reusable business logic close to the data; reduces network round-trips versus sending raw SQL from an app every time |
| Function | Similar to a procedure, but must return a value and can be used directly inside a SELECT/WHERE, like a built-in function | Reusable calculations or lookups you can embed in ordinary queries |
| Trigger | Code that runs automatically in response to an INSERT/UPDATE/DELETE on a table | Enforces rules or side effects (like audit logging) that can’t be expressed as a simple constraint |
A Query That Touches Several of These at Once
This is a preview — you’re not expected to write this yet — but it’s worth seeing how these objects compose in real code:
-- A view, built on a table with constraints already protecting its data
CREATE VIEW dbo.vw_ActiveCustomers AS
SELECT customer_id, name, email
FROM dbo.Customer
WHERE is_active = 1;
-- Querying the view feels identical to querying a table
SELECT * FROM dbo.vw_ActiveCustomers WHERE name LIKE 'A%';What You’ll Master First
In the beginner track you’ll fully master tables and constraints — the foundation everything else sits on. Views, indexes, stored procedures, functions, and triggers get a first practical preview along the way (functions in Chapter 4, views and temp tables in Chapter 5), with their full deep dive — including performance implications of indexes, and writing your own procedures/triggers — reserved for the advanced, developer/DBA-focused track that follows this one.
Why This Map Matters
Beginners often treat SQL as “just SELECT statements.” It’s not — it’s an ecosystem, and conflating “a query” with “the whole toolkit” causes two specific real mistakes: writing the same complex JOIN logic repeatedly in application code instead of wrapping it in a view, and validating data only in the application layer instead of also using constraints — which means a bug in one app, or a direct database edit by anyone, can silently corrupt data that a CHECK constraint would have blocked for free. Knowing this map exists means you’ll reach for the right tool later instead of forcing every problem through a plain query.
Enjoyed this?
Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.