Memory hook: Keys define relationships; transactions preserve consistency; indexes trade reads against writes.
Must remember
- Tables contain rows/columns with data types. Primary keys identify rows; foreign keys express supported relationships. Normalisation reduces redundancy/update anomalies; deliberate denormalisation can improve selected reads with consistency costs.
- ACID means atomicity, consistency, isolation and durability. A transaction groups changes; isolation controls interaction with concurrent work. An index accelerates suitable lookups but consumes space and adds write/maintenance work.
- DDL defines structures (
CREATE,ALTER); DML changes data (INSERT,UPDATE,DELETE);SELECTqueries it. Views store query definitions; stored procedures package operations; indexes are access structures, not duplicate business tables to edit directly. - SQL Database is managed relational database service; SQL Managed Instance supports more instance-level compatibility; SQL Server on Azure VMs provides greater guest/instance control with more customer maintenance. Azure Database for PostgreSQL/MySQL provide managed open-source engines.
- Read query requirements carefully: joins combine related rows; aggregates summarise;
WHEREfilters rows; null represents missing/unknown rather than zero. Managed SQL does not eliminate customer schema, query, access or recovery-design responsibilities.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need SQL Server OS/instance control | SQL Server on a VM. |
| Need managed per-database relational service | SQL Database. |
| Need repeatable query projection | A view where its semantics fit. |
Traps
- Every added index has a write/storage cost.
- Null and zero mean different things.
- A managed database does not design its schema for you.
Active recall
1. What does a primary key establish?
A unique row identity under its defined constraints.
2. Which ACID property groups all-or-nothing changes?
Atomicity.
3. What is a foreign key for?
To express/enforce a supported relationship between tables.
4. Does a view normally store a separate full copy of rows?
An ordinary view stores a query definition; materialised/indexed variants have different behaviour.
5. Why might Managed Instance suit migration?
Its supported instance-level compatibility can reduce changes compared with a more restricted database offering.