Overview
Domain 2 tests relational databases in two functional groups. The first is relational concepts: the features of relational data such as tables, primary and foreign keys, and enforced data types; normalization and why designers use it; common SQL statements; and common database objects such as views, stored procedures and indexes.
The second group is the relational services on Azure. You need to describe the Azure SQL family, which Microsoft lists as Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure Virtual Machines, and to identify Azure Database for PostgreSQL and Azure Database for MySQL as the managed services for open-source engines. Questions stay at the level of choosing and recognizing, not configuring, so the skill is reading a scenario and matching it to the right concept or service.
How it's tested
Items here usually show a short SQL statement, a table design or a migration requirement, then ask what the statement does, which normal form problem exists, or which Azure service fits. Product choice items hinge on how much control or compatibility the scenario needs and on whether the engine is SQL Server or open source.
-
Name the statement type
A query using CREATE, ALTER or DROP is DDL, one using SELECT, INSERT, UPDATE or DELETE is DML, and GRANT or REVOKE is DCL.
-
Pick the object
Saved query logic points to a view, reusable parameterized code to a stored procedure, and faster lookups to an index.
-
Choose the SQL platform
Near-full SQL Server compatibility with managed patching suggests Managed Instance, while operating system access suggests SQL Server on a VM.
A company moves an on-premises SQL Server application that uses SQL Server Agent jobs and cross-database queries, and wants Microsoft to handle patching and backups. SQL Server on a VM would work but leaves patching with the company. Azure SQL Database is fully managed but lacks instance-level features like SQL Server Agent. Azure SQL Managed Instance provides near-complete engine compatibility as a managed service, so it is the best fit.
Functional groups 2
Official functional groups from the Microsoft outline, in outline order.
2.1 Describe relational conceptsFeatures of relational data, normalization, common SQL statements and database objects.
- What it means
- This group covers identifying features of relational data, describing normalization and why it is used, identifying common structured query language (SQL) statements, and identifying common database objects.
- What to understand
- Relational data lives in tables of rows and columns with defined data types. A primary key uniquely identifies each row, and a foreign key references a key in another table to model relationships. Normalization splits data into related tables so each fact is stored once, reducing duplication and update anomalies; first, second and third normal form are the common steps. SQL statements fall into DDL for defining objects (CREATE, ALTER, DROP), DML for working with rows (SELECT, INSERT, UPDATE, DELETE) and DCL for permissions (GRANT, DENY, REVOKE). Common objects are views, which are saved queries; stored procedures, which hold reusable code with parameters; and indexes, which speed searches at a cost to writes and storage.
- How it shows up
- Expect a statement to classify, a table with repeated data to diagnose, or a requirement that maps to a view, procedure or index.
- Common confusion
- Confusing DELETE, which removes rows, with DROP, which removes the whole table definition.
2.2 Describe relational Azure data servicesThe Azure SQL family and Azure database services for open-source engines.
- What it means
- This group covers describing the Azure SQL family of products, including Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure Virtual Machines, and identifying Azure database services for open-source database systems.
- What to understand
- SQL Server on Azure Virtual Machines is infrastructure as a service: full control of the operating system and engine version, with the customer patching and managing it. Azure SQL Managed Instance is platform as a service with near-complete SQL Server compatibility, including SQL Server Agent and cross-database queries, suited to lift-and-shift moves. Azure SQL Database is a fully managed single database or elastic pool, ideal for new cloud applications, with automatic patching, backups and high availability. For open-source engines, Azure Database for PostgreSQL flexible server and Azure Database for MySQL flexible server provide managed versions with built-in backups, scaling and high availability options.
- How it shows up
- Expect a scenario with an engine, a compatibility need or a control requirement, and you pick the matching service.
- Common confusion
- Picking Azure SQL Database for a PostgreSQL application; SQL Database runs the SQL Server engine only.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Primary key
Column or columns that uniquely identify a row.
- Remember
- Must be unique and not null.
- Tested as
- Table design questions.
- Trap
- Confusing it with a foreign key.
-
Foreign key
Column that references a key in another table.
- Remember
- Enforces relationships between tables.
- Tested as
- Relationship questions.
- Trap
- Thinking it must be unique.
-
Normalization
Splitting data so each fact is stored once.
- Remember
- Reduces duplication and update anomalies.
- Tested as
- Design questions.
- Trap
- Assuming it speeds every query.
-
DDL
Data definition language.
- Remember
- CREATE, ALTER, DROP change structure.
- Tested as
- Statement classification.
- Trap
- Calling DROP a row operation.
-
DML
Data manipulation language.
- Remember
- SELECT, INSERT, UPDATE, DELETE work with rows.
- Tested as
- Statement classification.
- Trap
- Labeling SELECT as DDL.
-
DCL
Data control language.
- Remember
- GRANT, DENY, REVOKE manage permissions.
- Tested as
- Security statement questions.
- Trap
- Mixing it up with DDL.
-
View
Virtual table defined by a stored query.
- Remember
- Holds no data of its own in the basic form.
- Tested as
- Database object questions.
- Trap
- Thinking it copies the data.
-
Stored procedure
Named, reusable SQL code that accepts parameters.
- Remember
- Runs on the server for repeatable logic.
- Tested as
- Database object questions.
- Trap
- Confusing it with a view.
-
Index
Structure that speeds lookups on columns.
- Remember
- Faster reads, slower writes and extra storage.
- Tested as
- Performance questions.
- Trap
- Assuming more indexes are always better.
-
Azure SQL Database
Fully managed PaaS database or elastic pool.
- Remember
- Lowest admin effort; no instance-level features like SQL Server Agent.
- Tested as
- Service choice questions.
- Trap
- Choosing it for full SQL Server compatibility.
-
Azure SQL Managed Instance
PaaS instance with near-complete SQL Server compatibility.
- Remember
- Best for lift-and-shift with minimal changes.
- Tested as
- Migration questions.
- Trap
- Expecting operating system access.
-
SQL Server on Azure VMs
SQL Server installed on an IaaS virtual machine.
- Remember
- Full OS and engine control; customer manages patching.
- Tested as
- Control-requirement questions.
- Trap
- Thinking Azure patches it automatically like PaaS.
-
Azure Database for PostgreSQL
Managed PostgreSQL service, flexible server deployment.
- Remember
- Open-source engine with managed backups and HA.
- Tested as
- Open-source questions.
- Trap
- Confusing it with Azure SQL Database.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Azure SQL family
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| SQL Server on Azure VMs | IaaS virtual machine with SQL Server | Full control, customer manages OS and patching | Assuming Microsoft patches the engine |
| Azure SQL Managed Instance | PaaS instance in a virtual network | Near-full compatibility with managed operations | Expecting OS-level access |
| Azure SQL Database | PaaS single database or elastic pool | Least administration, fewer instance features | Choosing it when SQL Server Agent is required |
SQL statement families
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| DDL | Defines and changes database objects | CREATE, ALTER, DROP | Treating TRUNCATE or DROP as DML |
| DML | Reads and changes data in tables | SELECT, INSERT, UPDATE, DELETE | Thinking UPDATE changes the schema |
| DCL | Controls access to objects | GRANT, DENY, REVOKE | Classifying GRANT as DDL |
Common database objects
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| View | Saved SELECT presented as a table | Simplifies and restricts data access | Expecting it to store a copy of the rows |
| Stored procedure | Parameterized server-side code | Encapsulates repeatable operations | Using it where a simple view is asked for |
| Index | Lookup structure on columns | Speeds queries but costs write time | Indexing every column by default |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
DELETE removes the table
- What candidates pick
- Use DELETE to remove a table from the database.
- Why it looks right
- Delete sounds like removal of anything.
- Why it's wrong
- DELETE only removes rows; DROP TABLE removes the table definition and data.
Clue to watch for The requirement says the table itself should no longer exist.
-
View stores data
- What candidates pick
- A view keeps its own copy of the query results.
- Why it looks right
- It looks and queries like a table.
- Why it's wrong
- A standard view is only a stored query that runs against base tables each time.
Clue to watch for Wording about a saved query or virtual table.
-
Managed Instance for new SaaS apps
- What candidates pick
- Build a new multitenant cloud app on Managed Instance.
- Why it looks right
- It has the most features.
- Why it's wrong
- Azure SQL Database with elastic pools is the expected choice for new cloud-born applications.
Clue to watch for No legacy dependencies and a focus on minimal management.
-
VM for minimal management
- What candidates pick
- Choose SQL Server on Azure VMs to reduce admin effort.
- Why it looks right
- It feels closest to on-premises.
- Why it's wrong
- A VM keeps patching and OS upkeep with the customer, which increases effort.
Clue to watch for The scenario stresses reducing administration.
-
SQL family for MySQL
- What candidates pick
- Host a MySQL app on Azure SQL Database.
- Why it looks right
- Both are SQL databases.
- Why it's wrong
- Azure SQL runs the SQL Server engine; MySQL needs Azure Database for MySQL.
Clue to watch for The scenario names an open-source engine.
-
Indexes always help
- What candidates pick
- Add indexes to every column to speed the system.
- Why it looks right
- Indexes speed queries.
- Why it's wrong
- Each index slows inserts and updates and uses storage, so heavy-write tables suffer.
Clue to watch for A write-heavy transactional table.
Answer framework
Apply these steps to relational concept and Azure relational service questions.
- If a statement is shown, find the first keyword and classify it as DDL, DML or DCL before reading the answer choices.
- If a table design is shown, look for repeated values or groups that indicate missing normalization and a need for separate related tables.
- If an object is described, decide whether it is a saved query, reusable parameterized code or a lookup structure.
- For service choice, identify the engine first: SQL Server points to the Azure SQL family, PostgreSQL or MySQL to their Azure Database services.
- Within the Azure SQL family, weigh control against management effort: VM for full control, Managed Instance for compatibility, SQL Database for least administration.
Practice question
Want more like this? The DP-900 practice test has questions for every domain.
Checklist
0 of 12
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Split this domain into theory and services. For theory, take a small denormalized table, such as orders with repeated customer details, and normalize it on paper into customers, orders and order lines with keys. Then write one example each of CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE and GRANT, and label each with its family. Running these in a free Azure SQL Database or a local SQL Server container makes the categories stick.
For services, build a comparison grid with the three Azure SQL options and the two open-source services as columns, and rows for engine, management model, OS access, compatibility and typical use case. Practice with short scenarios: a legacy app with SQL Server Agent jobs, a new SaaS product with many tenants, a vendor system that needs a specific Windows setting, and a PostgreSQL application. Microsoft Learn's relational data module on Azure covers exactly these points, so finish with its knowledge checks and note any wording that surprised you.
FAQ
How much of DP-900 is Domain 2, Identify considerations for relational data on Azure?
Microsoft weights the relational data domain at 20–25% of DP-900, with two functional groups: relational concepts and relational Azure data services.
Do I need to write SQL for DP-900?
You need to read and classify simple statements, such as recognizing DDL, DML and DCL, but not to write complex queries.
What is normalization in simple terms?
Organizing data into related tables so each fact is stored once, which avoids duplication and inconsistent updates.
When is Azure SQL Managed Instance the right answer?
When an existing SQL Server workload needs near-full engine compatibility, such as SQL Server Agent or cross-database queries, but the customer wants a managed service.
Which Azure services run open-source relational databases?
Azure Database for PostgreSQL and Azure Database for MySQL, both offered as flexible server deployments.
Is a view the same as a table?
No. A standard view is a saved query that returns data from base tables when used and stores no rows itself.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 2 knowledge
You have covered keys, normalization, SQL statement families, database objects and the Azure relational services. Check whether you can classify statements and choose the right platform.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the DP-900 AI tutor Get any concept explained your way