Study guide

3.5 Relational Data Tasks and Tools

Microsoft Azure Data Fundamentals DP-900

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Introduction & Exam Overview

1.1 Current Exam Facts 1.2 How to Use This Guide

2 Chapter 2: Core Data Concepts

2.1 Data Roles and Workloads 2.2 Relational vs Non-Relational Data 2.3 Transactional Workloads (OLTP) and ACID 2.4 Analytical Workloads (OLAP) and Architectures 2.5 Batch vs Stream Processing

3 Chapter 3: Relational Data on Azure

3.1 Azure SQL Family Overview 3.2 Azure SQL Database Pricing & Performance Tiers 3.3 Azure Database for PostgreSQL/MySQL/MariaDB 3.4 Azure Cosmos DB for PostgreSQL 3.5 Relational Data Tasks and Tools

4 Chapter 4: Non-Relational Data on Azure

4.1 Azure Storage Account and Services 4.2 Azure Blob Storage 4.3 Azure Files, Queue, and Table Storage 4.4 Azure Cosmos DB: Use Cases and Architecture 4.5 Azure Cosmos DB APIs 4.6 Azure Cosmos DB Consistency Levels

5 Chapter 5: Analytics Workloads on Azure

5.1 Modern Data Warehouse and Lakehouse 5.2 Azure Synapse Analytics 5.3 Azure Data Factory and Pipelines 5.4 Azure Stream Analytics and Real-Time Ingestion 5.5 Microsoft Fabric 5.6 Power BI Basics for Data Fundamentals

3.5 Relational Data Tasks and Tools

Key Takeaways Explain This Topic AI

T-SQL statements fall into four families: DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE, MERGE), DCL (GRANT, REVOKE, DENY), and TCL (BEGIN, COMMIT, ROLLBACK).

Common Azure SQL admin tasks include provisioning, configuring server-level and database-level firewall rules , enabling Microsoft Entra ID (formerly Azure AD) authentication, and turning on Transparent Data Encryption (TDE) , which is on by default for Azure SQL Database.

Azure SQL Database performs automatic backups with point-in-time restore (PITR) for 7 to 35 days, plus optional long-term retention (LTR) for up to 10 years.

Query Performance Insight (in the Azure portal) and SQL Insights (in Azure Monitor) are the built-in tools for monitoring resource usage and identifying slow queries.

Day-to-day work uses Azure portal , Azure Data Studio , SQL Server Management Studio (SSMS) , Azure CLI , PowerShell , and ARM/Bicep templates — the same tool family across every Azure SQL deployment option.

Last updated: June 2026

The Four T-SQL Statement Families

Quick Answer: Every action you take with Transact-SQL (T-SQL) falls into one of four categories. DDL changes structure, DML changes data, DCL changes permissions, and TCL controls transactions.

  • Category Purpose Common Statements
  • DDL — Data Definition Language Define and change schema objects CREATE , ALTER , DROP , TRUNCATE
  • DML — Data Manipulation Language Read and change row data SELECT , INSERT , UPDATE , DELETE , MERGE
  • DCL — Data Control Language Manage permissions GRANT , REVOKE , DENY

TCL — Transaction Control Language Group statements into atomic units BEGIN TRANSACTION , COMMIT , ROLLBACK , SAVE TRANSACTION

A typical transactional flow combines all four: BEGIN TRANSACTION;

  • UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
  • UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
  • IF @@ERROR <> 0
  • ROLLBACK TRANSACTION;
  • ELSE
  • COMMIT TRANSACTION;
  • Common Relational Tasks on Azure
  • Provisioning

You create an Azure SQL Database from the Azure portal, the Azure CLI ( az sql db create ), PowerShell ( New-AzSqlDatabase ), or declaratively with an ARM or Bicep template. Every option needs a logical server, a database name, a pricing tier, and a region.

Securing Access

Three layers protect a database:

Network : server-level firewall rules , database-level firewall rules, Private Endpoints , and VNet integration for Managed Instance.

Authentication : SQL authentication (username/password) and Microsoft Entra ID (formerly Azure Active Directory) authentication, which is the recommended option because it supports MFA, conditional access, and managed identities.

Authorization : server- and database-level roles, schemas, and granular GRANT / REVOKE permissions.

Advanced features include Dynamic Data Masking , Row-Level Security (RLS) , and Always Encrypted for client-side column encryption.

Encryption

Transparent Data Encryption (TDE) encrypts data at rest in the database files, log files, and backups. It is enabled by default for Azure SQL Database and Managed Instance. You can let Microsoft manage the key (service-managed) or supply your own through Azure Key Vault (customer-managed key / Bring Your Own Key). Data in transit is encrypted with TLS.

Backups and Restore

Azure SQL Database takes automatic backups: a full backup weekly, differential backups every 12–24 hours, and transaction log backups roughly every 5–10 minutes.

Point-in-Time Restore (PITR) restores to any second within the retention window (default 7 days, configurable up to 35 days).

Long-Term Retention (LTR) keeps weekly, monthly, or yearly backups for up to 10 years for compliance.

Restores create a new database ; the original is not overwritten.

High Availability and Disaster Recovery

Business Critical and Premium tiers run an Always On cluster on local SSD for sub-second failover.

Zone-redundant configuration spreads replicas across Azure Availability Zones.

Active geo-replication and auto-failover groups replicate to a different region for disaster recovery.

Monitoring

Query Performance Insight is a portal blade that surfaces the top resource-consuming queries.

Automatic Tuning can create or drop indexes and force last-known-good plans without manual intervention.

SQL Insights (in Azure Monitor ) provides deeper metrics, log queries, and alerting across many databases.

The Tooling Stack

  • Tool What It Is When to Use
  • Azure portal Browser-based admin UI Provisioning, firewall rules, monitoring blades
  • Azure Data Studio Cross-platform query and notebook tool Daily query and authoring on macOS, Linux, Windows

SQL Server Management Studio (SSMS) Windows-only full-featured admin tool Heavy SQL Server administration tasks

  • Azure CLI ( az sql ... ) Cross-platform command-line Scripting, CI/CD pipelines
  • Azure PowerShell ( Az.Sql ) PowerShell cmdlets Windows-centric automation
  • ARM / Bicep templates Declarative infrastructure-as-code Repeatable, source-controlled deployments
  • sqlcmd / mssql-cli Lightweight command-line query tools Quick scripts, container/edge environments

Because every Azure SQL service speaks T-SQL, the same toolset works across Azure SQL Database, Managed Instance, SQL Server on Azure VMs, and Azure SQL Edge — the main difference is what you can administer, not how you connect.

What to Remember for the Exam

Be able to map each T-SQL statement to its family (DDL/DML/DCL/TCL).

Know that TDE is on by default and that PITR covers 7–35 days while LTR covers up to 10 years.

Recognize Microsoft Entra ID authentication as the modern, recommended option.

Match each tool to its use case — for example, Azure Data Studio for cross-platform query work versus SSMS for full administrative depth on Windows.

Common Database Objects

The DP-900 relational domain explicitly lists "identify common database objects," so know what each is and what it does.

  • Object Purpose
  • Table Stores rows and columns; the core data container
  • View A saved SELECT that presents data as a virtual table, simplifying or securing access
  • Index A structure (clustered or nonclustered) that speeds up lookups at the cost of write overhead
  • Stored procedure Precompiled T-SQL that takes parameters and runs logic on the server
  • Function Returns a value or table; usable inside queries
  • Trigger T-SQL that fires automatically on INSERT/UPDATE/DELETE
  • Constraint Rules (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT) that enforce integrity

A clustered index defines the physical order of the table (one per table); a nonclustered index is a separate structure pointing back to rows (many allowed). Views and stored procedures also serve as a security layer : you can grant access to a view or procedure without granting access to the underlying tables.

A SELECT Anatomy

Because the exam expects you to "identify common SQL statements," be comfortable reading a basic query even though you will not write production code: SELECT c.CustomerName, SUM(o.Total) AS Revenue

  • FROM Customers AS c
  • JOIN Orders AS o ON o.CustomerId = c.CustomerId
  • WHERE o.OrderDate >= '2026-01-01'
  • GROUP BY c.CustomerName
  • HAVING SUM(o.Total) > 1000
  • ORDER BY Revenue DESC;

Recognize the clauses: SELECT chooses columns, FROM / JOIN choose tables and relate them, WHERE filters rows before grouping, GROUP BY aggregates, HAVING filters the aggregated groups, and ORDER BY sorts the result. Knowing that WHERE filters rows and HAVING filters groups is a classic distractor.

Authentication and Authorization, Restated

For the exam, separate the two ideas cleanly: authentication is proving who you are (SQL login or Microsoft Entra ID ), while authorization is what you are allowed to do (roles plus GRANT / REVOKE / DENY ). Microsoft Entra authentication is preferred because it enables MFA, conditional access, and managed identities , so applications connect with no stored password. This security framing recurs across the relational and non-relational domains alike.