DP-900: how to use schemas in relational databases on Azure

I will teach the skill of understanding and using schemas in relational databases on Azure — that is, how to model tables, columns and types, and how the schema influences integrity, performance and queries. This skill is common on the DP-900 because it shows you understand relational fundamentals and their application on Azure.

What you need to know

A schema in relational databases describes the structure: tables, columns, data types, constraints (for example PRIMARY KEY, FOREIGN KEY, UNIQUE), and indexes. On Azure, services like Azure SQL Database and Azure Database for PostgreSQL use traditional relational schemas. Knowing how to interpret the schema allows you to answer questions about referential integrity, query capability and suitability of types for real-world scenarios.

Simple example: a sales database can have two tables — Customers and Orders. The schema defines that Orders.CustomerID is a FOREIGN KEY that references Customers.CustomerID, ensuring there are no orders without a valid customer.

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name NVARCHAR(100) NOT NULL,
  Email NVARCHAR(320) UNIQUE
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderDate DATETIME2 NOT NULL,
  CustomerID INT NOT NULL,
  Amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

How it works

The schema serves several practical functions:

• Validation and integrity: data types and NOT NULL prevent invalid values; FOREIGN KEY maintains integrity between tables.
• Performance: choosing correct types (for example INT instead of NVARCHAR when appropriate) reduces space and speeds operations; indexes (associated with schema columns) accelerate queries.
• Semantic clarity: the schema documents the intent of the data — e.g., an Email field with UNIQUE prevents duplicates.

On Azure, beyond creating the schema, you have options like collation for sorting and comparing strings, and specific supported types (for example DATETIME2 in Azure SQL Database). It is also important to understand how schema changes affect availability and performance in managed environments: DDL operations can lock objects, so they should be planned.

In practice

Here is a small practical flow to create and validate a schema in Azure SQL Database (the conceptual steps apply to other relational engines):

1. Model on paper: draw entities and relationships. Decide data types and which columns are keys.

2. Choose types and constraints: use appropriate types (INT, DECIMAL, NVARCHAR) and constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL) to ensure integrity.

3. Write DDL: create the tables with CREATE TABLE. Test inserting valid and invalid data to confirm constraints.

4. Indexing: add indexes after analyzing frequent queries. Usually the PRIMARY KEY creates a clustered index; for columns used in filters/joins add nonclustered indexes.

5. Changes: to change the schema in production, use techniques like adding nullable columns first, updating in batches, and creating indexes offline to minimize impact.

-- Create a nonclustered index on OrderDate to speed queries by date
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);

-- Example CHECK to validate values
ALTER TABLE Orders ADD CONSTRAINT CHK_Amount_Positive CHECK (Amount >= 0);

Common mistakes

• Choosing overly generic types: using NVARCHAR(MAX) for everything wastes space and degrades performance. Prefer the smallest size that meets requirements.
• Lack of constraints: omitting PRIMARY/FOREIGN KEY and UNIQUE may speed ingestion, but makes ensuring integrity and later debugging harder.
• Adding indexes online without analysis: creating many indexes on write-heavy tables increases the cost of INSERT/UPDATE; analyze queries and load patterns before indexing.

How to practice

Practice these concepts by creating a database in Azure SQL Database (or using a local SQL Server instance) and implementing the flow above. Test constraints and performance with growing datasets; experiment with controlled schema changes.

To prepare for DP-900, use the official and free Practice Assessment from Microsoft (it is the correct way to practice the exam format). Also consult the free Microsoft Learn Study Guide for DP-900 — both are official and free resources that help you verify skills and review concepts.

In summary

• The schema defines structure, types and constraints — it is fundamental for integrity and understanding the data.
• Choosing correct types and using constraints reduces errors and improves performance.
• Indexing complements the schema: it optimizes queries but has a cost on writes.
• Practice on Azure with DDL, insertion tests and load simulation; use Microsoft’s official resources to prepare for the exam.