3.5 Relational Data Tasks and Tools

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.

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:

1. Network: server-level firewall rules, database-level firewall rules, Private Endpoints, and VNet integration for Managed Instance.
2. 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.
3. 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

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.

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.