Skip to main content
Brixus
  1. Courses
  2. Certification Study Guides
  3. DP-800 SQL AI Developer Study Guide & Exam Questions
  4. Read
DP-800 SQL AI Developer Study Guide & Exam Questions
  • 1.Part 1: Database Objects, Advanced T-SQL and AI-Assisted Development
    • 1. Tables, Indexes, Constraints, JSON Columns and Partitioning
    • 2. Views, Stored Procedures, Functions and Triggers
    • 3. Advanced T-SQL: CTEs and Window Functions
    • 4. JSON Functions in T-SQL: JSON_VALUE, OPENJSON and JSON_ARRAYAGG
    • 5. Advanced T-SQL: Regex, Fuzzy Matching, Graph and Error Handling
    • 6. GitHub Copilot, Copilot in Fabric and MCP for SQL Development
  • 2.Part 2: Security, Performance Tuning and CI/CD Deployment
  • 3.Part 3: AI in SQL: Embeddings, Vector Search and RAG

Your progress is not saved. You are trying this lesson as a guest, so your answers and progress are not saved.

1.1 Tables, Indexes, Constraints, JSON Columns and Partitioning

Design choices for tables, indexes, and specialized objects tend to stay with a system for years. Application code can be refactored quickly, but changing storage format, adding history tracking, or replacing an identity key with a shared number generator often requires rebuilding large tables and coordinating downtime.

This page explains how to choose structures that fit transactional, analytical, and AI-assisted workloads across SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric. The focus is on getting storage, integrity, and scale right at design time so later changes stay inexpensive.

Watch the video

Why object design matters early

A standard table selected where auditing was needed leads to custom triggers and history tables. An identity key selected where several tables must share one number series forces application workarounds. Special structures change storage, validation, and query behavior at the engine level, so they cannot be added as simple tuning later.

  • Performance — compact types and suitable indexes shorten execution and maintenance work

  • Data integrity — engine-enforced rules keep data consistent no matter which app or import writes it

  • Maintainability — clear objects simplify backup, tuning, and administration

  • Scalability — partitioning and columnar storage allow very large data sets to stay manageable

  • AI readiness — clean, well-structured data supports search, retrieval-augmented generation, and analytics integration

Choosing a SQL platform

Platform choice decides how much infrastructure you manage. With Infrastructure as a Service (IaaS) such as SQL Server on Azure Virtual Machines, you control the operating system, engine configuration, and availability topology. With Platform as a Service (PaaS) such as Azure SQL Database and SQL Managed Instance, Microsoft operates servers, networking, patching, and high availability, while you design tables, indexes, constraints, and data.

Diagram: On-premises - higher cost and admin, SQL Server on physical machines, SQL Server on Azure VM - IaaS, Off-premises - lower cost and admin, Azure SQL Managed Instance - PaaS, Azure SQL Database - PaaS SaaS, SQL Server Private Cloud, Hybrid cloud - middle balance, Hybrid cloud

On PaaS the boundary sits just below the database: physical hosts, operating system updates, and engine versions are handled for you, while schema and query design remain your responsibility. That division is why time spent on types, keys, and indexes pays off more than infrastructure tuning.

Azure SQL Database

Azure SQL Database is a fully managed database for transactional and operational analytics work. Hyperscale separates storage from compute so size can grow without a preset limit and you pay for storage actually used, with extra read replicas added for scale-out. Serverless compute scales with demand and pauses when idle, resuming automatically on connection, so retry logic and short transactions are important. Built-in intelligence suggests indexes and can correct regressed plans, backed by a 99.99% availability agreement.

Azure SQL Managed Instance

Managed Instance tracks the latest Enterprise Edition engine with automatic patching and native virtual network isolation. It suits lift-and-shift moves because agent jobs, Service Broker, linked servers, three-part cross-database names, and database mail continue to work. A link based on distributed availability groups keeps Azure nearly synchronized with on-premises SQL Server for hybrid reads, recovery, and low-downtime moves. The Business Critical tier adds In-Memory OLTP for memory-optimized tables and natively compiled procedures.

Diagram: Single instance - best for lift-and-shift, SQL Server surface area, Native virtual network support, Fully managed service, Instance pool, Shared resources to optimize price, Simplified management for many databases

SQL Server on virtual machines and SQL database in Fabric

SQL Server on Azure Virtual Machines gives full control over version, operating system, and Always On topology. An agent extension adds portal-driven backups, patch windows, Key Vault integration, tempdb setup, best-practice checks, and I/O analysis. SQL database in Fabric uses the same engine as Azure SQL Database but mirrors every committed change to OneLake as Delta Parquet without pipelines, exposing a read-only analytical endpoint so reports query the copy and leave transactions undisturbed. It also adds automatic indexing, Git integration, SqlPackage portability, GraphQL access, and support for semantic search.

Watch the video

A startup runs a transactional ordering system that must grow storage automatically and add read replicas for reports. A second team runs a legacy app needing a specific SQL Server version and operating-system access.

1. On a PaaS database such as Azure SQL Database, which responsibility remains with the developer when the provider manages everything below the database layer?
  • The operating system version and replica placement
  • Tables, indexes, constraints, and data
  • Physical servers and network switches
  • Engine patching and host maintenance
Show answer and explanation
  • Tables, indexes, constraints, and data

PaaS handles servers, networking, OS patches, and engine updates, leaving tables, indexes, constraints, and data to the developer.

2. A serverless Azure SQL Database that pauses when idle requires connection retry logic because the first connection after a pause can be delayed while the database resumes.
  • True
  • False
Show answer and explanation
  • True

Serverless pauses when idle and resumes on connection, so apps need retry handling and should avoid long transactions that block pause.

3. A Fabric SQL database automatically mirrors changes to OneLake. How should a heavy quarterly report query the data without slowing order processing?
  • Running reports directly against live operational tables for freshness
  • Querying the automatically mirrored Delta Parquet copy through the analytical endpoint
  • Building manual ETL triggers for every table change
  • Disabling automatic index creation during reporting hours
Show answer and explanation
  • Querying the automatically mirrored Delta Parquet copy through the analytical endpoint

Mirroring copies operational changes to OneLake as Delta Parquet, and analytical queries use that copy so operational tables stay fast.

Table design: types, columns, and sizing

Tables hold entities as rows and attributes as columns, including fact tables for measurable events and dimension tables for context. Type and length decisions affect storage, join speed, and whether production changes later force a rebuild.

Choose types when precision matters, when volume multiplies wasted bytes, and when narrow keys speed frequent filters and joins. A retail table holding 100 million rows per day wastes about 5 GB daily, or roughly 1.8 TB yearly, if each row carries 50 unneeded bytes.

Type Category

Data Types

Storage Size

Usage Guidelines

Numeric

INT, BIGINT, DECIMAL, FLOAT

4 bytes, 8 bytes, varies

Use DECIMAL for exact money values; reserve FLOAT for approximate science where rounding is acceptable

String

VARCHAR, CHAR, NVARCHAR

1 byte/char, fixed, 2 bytes/char

VARCHAR for variable ASCII, CHAR for fixed codes, NVARCHAR for Unicode

Date/Time

DATE, DATETIME2, DATETIMEOFFSET

3 bytes, 6-8 bytes, 10 bytes

Prefer DATETIME2 over legacy DATETIME for precision

Binary

VARBINARY, IMAGE

varies

For photos, files, and documents

Special

UNIQUEIDENTIFIER, XML, JSON

16 bytes, varies, native binary

GUIDs, XML documents, and native JSON on SQL Server 2025+

Warning

Using FLOAT for money can introduce rounding errors across dependent calculations, while a UNIQUEIDENTIFIER key instead of INT roughly triples index size and slows joins. A table storing 200 bytes per row instead of 100 doubles storage, backup time, and I/O.

Estimate size by adding fixed-type widths, average variable lengths, and about 7 bytes of row overhead. One million narrow employee rows near 99 bytes each occupy roughly 94 MB.

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

A sound product definition uses a 4-byte identity key, Unicode names sized to expected length, exact decimal pricing, defaults that avoid nulls, and a modern timestamp default.

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);
Note

NVARCHAR stores 2 bytes per character for international text, while VARCHAR stores 1 byte for ASCII-only data. A 30-character name uses about 60 bytes in NVARCHAR but 30 bytes in VARCHAR, saving roughly 300 MB over 10 million rows when Unicode is not needed.

  • Pick the smallest type that safely holds the range and precision

  • Estimate row and table size before planning storage and indexes

  • Enforce quality with NOT NULL, defaults, and constraints

  • Plan for growth and archive needs

  • Index WHERE, JOIN, and ORDER BY columns, preferring columnstore for large analytics

  • Balance normalization against query cost and consider compression for large tables

Watch the video

4. A university finance system must store tuition amounts such as 1250.75 without rounding errors. Which type should its Price column use and why?
  • FLOAT, because approximate storage is faster for money totals
  • DECIMAL(10,2), because it stores exact precision for financial amounts
  • BIGINT, because prices should always be integers
  • UNIQUEIDENTIFIER, because money needs globally unique keys
Show answer and explanation
  • DECIMAL(10,2), because it stores exact precision for financial amounts

DECIMAL stores exact base-10 values for money, while FLOAT is approximate binary and can round financial totals.

5. Match each column design to the data-type category that fits it best.
  • CountryCode CHAR(2) for 'US' values
  • Exact money values
  • Fixed-length codes
  • High-precision timestamps
  • Variable Unicode names
Show answer and explanation
  • CountryCode CHAR(2) for 'US' values: Fixed-length codes

Fixed-length codes such as a 2-letter country code suit CHAR.

6. Storing English-only product names in NVARCHAR(100) instead of VARCHAR(100) cuts string storage roughly in half.
  • True
  • False
Show answer and explanation
  • False

NVARCHAR uses about twice the bytes of VARCHAR, so ASCII-only data stored as NVARCHAR roughly doubles string storage.

Rowstore and columnstore indexes

Without an index the engine scans every row, like reading a whole book instead of using its index. Indexes turn large scans into a few lookups but consume space and slow writes because each insert, update, and delete must maintain them.

Rowstore keeps all columns of a row together on a page, ideal for transactions that fetch whole records. A clustered index defines physical row order and only one can exist per table. A nonclustered index is a separate structure with key values plus row pointers, and many can exist, optionally with INCLUDE columns to cover queries without extra lookups.

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

Use a clustered key for the main access path such as a narrow identity or date range. Use nonclustered indexes for selective predicates, join keys, and sort orders that differ from the clustered order, weighing write cost.

Columnstore architecture

Analytical aggregates over millions of rows waste effort reading unneeded columns from rowstore. Columnstore stores each column separately and compresses it independently so queries read only required segments. Data is grouped into rowgroups of up to 1,048,576 rows. Small inserts land in a rowstore deltastore built as a B-tree; once a delta rowgroup reaches at least 102,400 rows a background tuple-mover compresses it, while bulk loads of that size or larger compress directly.

Scenario

Recommendation

Reason

Warehouse fact tables with millions of rows

Use columnstore

Columnar compression and batch scans speed aggregates

Reporting databases with heavy reads

Use columnstore

Column-only reads accelerate sums and averages

Rarely updated historical archives

Use columnstore

High compression with few modifications

Small tables under 1 million rows

Avoid columnstore

Rowgroup overhead exceeds benefit

High-frequency updates or deletes

Avoid columnstore

Changes mark rows deleted and fragment the store

Single-row lookups

Avoid columnstore

Rowstore seeks are faster for one record

A clustered columnstore index (CCI) replaces rowstore storage and holds the whole table in columnar form for analytics-first work. A nonclustered columnstore index (NCCI) keeps rowstore for transactions and adds a columnar copy of chosen columns, letting the optimizer pick the better path per query.

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;
-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

Health can be checked in sys.dm_db_column_store_row_group_physical_stats, where open means still accepting deltastore inserts, closed means awaiting compression, and compressed means columnar. Many small groups or high deleted counts signal fragmentation fixed by REORGANIZE or REBUILD.

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

Watch the video

7. An online store needs fast single-row order lookups plus fast price and stock aggregates on the same Product table. Which columnstore choice fits and why?
  • Use CCI to keep rowstore for orders and add a second copy for reports
  • Use NCCI to keep rowstore for single-row orders while adding columnar access for aggregates
  • Use CCI because it creates a separate secondary copy alongside rowstore
  • Avoid both and rely only on nonclustered rowstore for aggregates
Show answer and explanation
  • Use NCCI to keep rowstore for single-row orders while adding columnar access for aggregates

CCI stores all data columnar without rowstore, while NCCI keeps rowstore for OLTP and adds a columnar copy for analytics on the same table.

8. Order the stages by which new rows flow from transactional insert into compressed columnstore storage.
  • Delta rowgroup reaches at least 102,400 rows or a bulk load arrives
  • Queries read only needed column segments with compression benefits
  • Small inserts accumulate in the deltastore rowstore structure
  • Tuple-mover compresses the group into columnar segments, or bulk data compresses directly
Show answer
  1. Small inserts accumulate in the deltastore rowstore structure
  2. Delta rowgroup reaches at least 102,400 rows or a bulk load arrives
  3. Tuple-mover compresses the group into columnar segments, or bulk data compresses directly
  4. Queries read only needed column segments with compression benefits
9. A 50,000-row lookup table with frequent single-row seeks and constant updates should use a clustered columnstore index for best performance.
  • True
  • False
Show answer and explanation
  • False

Small tables under about 1 million rows and single-row lookup patterns favor rowstore; columnstore overhead and fragmentation hurt those cases.

Specialized table types

Standard disk tables cannot efficiently cover every pattern. Memory speed, automatic history, lake data, tamper evidence, and multi-hop relationships each have a dedicated structure.

In-memory optimized tables

Disk latency limits workloads needing thousands of transactions per second. In-memory tables hold data in RAM with lock-free optimistic concurrency for session state, trading engines, order intake, hot reference caches, and ETL staging. A shopping-cart cache, for example, can serve tens of thousands of concurrent carts with sub-millisecond response. Sizes are bounded by memory, large types like VARCHAR(MAX) are unsupported, and durability still relies on transaction logs written to disk for recovery.

-- Create in-memory optimized table
CREATE TABLE dbo.OrderCache (
    OrderID INT PRIMARY KEY NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME2,
    Amount DECIMAL(10,2),
    INDEX IX_CustomerID NONCLUSTERED (CustomerID)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Temporal tables

Temporal tables version every update and delete into a linked history table with validity timestamps, using normal INSERT, UPDATE, and DELETE statements. Queries such as FOR SYSTEM_TIME AS OF can reconstruct salaries, stock, or balances at a past date for audit, debugging, trend study, recovery, and slowly changing dimensions. Storage roughly doubles and old history can be cleaned automatically.

-- Create temporal table with automatic history tracking
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Department NVARCHAR(50),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2026-01-01' 
WHERE EmployeeID = 1;

External tables

External tables virtualize Parquet or CSV files in lake or blob storage so they can be joined without import, useful for log exploration, federated joins, and cold archives. They avoid duplication but pay network and parsing cost, are generally read-only, and offer limited indexing.

-- Create external table pointing to data lake
CREATE EXTERNAL TABLE dbo.ExternalSalesData (
    OrderID INT,
    CustomerID INT,
    OrderAmount DECIMAL(10,2),
    OrderDate DATE
) WITH (
    LOCATION = '/raw/sales/',
    DATA_SOURCE = DataLakeSource,
    FILE_FORMAT = ParquetFormat
);

Ledger and graph tables

Ledger tables add blockchain-inspired hashes linking each operation, creating tamper-evident history verified through views like sys.database_ledger_transactions and sp_verify_database_ledger. Updatable ledgers permit inserts, updates, and deletes with cryptographic versioning and can combine with temporal querying, while append-only ledgers permit only inserts for immutable logs such as trial results or payment records. Graph tables model nodes with AS NODE and relationships with AS EDGE, using hidden $node_id, $edge_id, $from_id, and $to_id columns plus MATCH syntax to simplify multi-hop traversals like reporting chains or recommendations. They are less useful for simple foreign-key hierarchies or purely transactional schemas.

-- Create ledger table
CREATE TABLE dbo.FinancialTransaction (
    TransactionID INT PRIMARY KEY IDENTITY,
    AccountNumber NVARCHAR(20),
    Amount DECIMAL(15,2),
    TransactionType NVARCHAR(20)
) WITH (LEDGER = ON);

-- Append-only ledger provides immutability
CREATE TABLE dbo.AuditLog (
    LogID INT PRIMARY KEY IDENTITY,
    EventDescription NVARCHAR(500),
    EventTimestamp DATETIME2
) WITH (LEDGER = ON, APPEND_ONLY = ON);
-- Create graph tables
CREATE TABLE Person AS NODE;
CREATE TABLE Manages AS EDGE;
CREATE TABLE Knows AS EDGE;

-- Insert nodes
INSERT INTO Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

-- Insert edges (relationships)
INSERT INTO Manages VALUES (1, 2), (2, 3);

-- Query relationships
SELECT Person1.name, Person2.name 
FROM Person AS Person1, Manages, Person AS Person2
WHERE MATCH (Person1-(Manages)->Person2)
AND Person1.id = 1;
Tip

Select the special type at design time: memory needs RAM, history doubles space, external adds latency, ledger restricts deletion, and graph needs new query syntax.

Watch the video

10. Match each application need to the specialized table type that solves it.
  • 50,000 concurrent shopping carts needing sub-millisecond access
  • Reconstructing an employee salary as of January 1
  • Joining live orders with years of Parquet logs in a lake
  • Proving clinical trial results were never altered
  • Append-only ledger table
  • External table
  • In-memory optimized table
  • Temporal table
Show answer and explanation
  • 50,000 concurrent shopping carts needing sub-millisecond access: In-memory optimized table
  • Reconstructing an employee salary as of January 1: Temporal table
  • Joining live orders with years of Parquet logs in a lake: External table
  • Proving clinical trial results were never altered: Append-only ledger table

Millisecond carts and trading workloads need RAM-based optimistic concurrency.

Temporal validity columns answer as-of questions without custom audit code.

External tables query lake files where they live without import.

Append-only ledgers give cryptographic proof that trial data was unaltered.

11. A social app must find friends-of-friends across three relationship hops efficiently. Which modeling approach simplifies that traversal?
  • Foreign keys with recursive application loops
  • Node and edge tables queried with MATCH
  • External tables over Parquet files
  • In-memory staging tables with hash indexes
Show answer and explanation
  • Node and edge tables queried with MATCH

MATCH traverses $from_id to $to_id across node and edge tables, avoiding many recursive joins for multi-hop paths.

Constraints and sequence objects

Application checks can be skipped by bulk loads, direct SQL, or new services. Engine constraints run on every write, making them the reliable place to block orphans, duplicates, invalid values, and missing data.

Problem

Constraint

Example

Orphaned records

FOREIGN KEY

Block orders that reference a missing customer

Duplicate data

UNIQUE

Stop a second registration with the same email

Invalid data

CHECK

Reject negative prices or future birth dates

Missing critical data

NOT NULL

Require essential columns

Cross-table inconsistency

FOREIGN KEY

Keep parent-child links valid

A missing unique rule on email, for instance, lets the same address register repeatedly, causing duplicate marketing mail and extra cost. A single UNIQUE definition would have rejected the second insert.

  • Primary key — one per table, NOT NULL, backed by a unique index for entity integrity

  • Foreign key — enforces referential integrity with CASCADE, SET NULL, or SET DEFAULT; add indexes on join columns for speed

  • Unique — allows one NULL per column and builds a unique nonclustered index for alternate keys like SKU

  • Check — accepts any TRUE/FALSE expression; NULL evaluates to unknown so nullable columns can still accept nulls unless also NOT NULL

  • Default — supplies values such as GETUTCDATE() or 1 on insert; name defaults explicitly like DF_Activity_CreatedDate for consistent deployments

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    EmailAddress NVARCHAR(100) NOT NULL
);
CREATE TABLE Activity (
    ActivityID INT PRIMARY KEY IDENTITY,
    Description NVARCHAR(200),
    CreatedDate DATETIME2 CONSTRAINT DF_Activity_CreatedDate DEFAULT GETUTCDATE(),
    IsActive BIT CONSTRAINT DF_Activity_IsActive DEFAULT 1
);

Sequences versus identity

Identity auto-numbers one table simply but cannot share values, pre-allocate before insert, or change its step easily. A sequence is an independent schema object that any table can call with NEXT VALUE FOR, supports custom bounds, cycling, ordering by another field with OVER, bulk reservation with sp_sequence_get_range, and later alteration of the increment.

Feature

Sequence

Identity

Tied to one table

No

Yes

Shared across tables or columns

Yes

No

Fetch value before insert

Yes

No

Custom min and max

Yes

Limited

Reserve a block at once

Yes

No

Cycle or restart at a bound

Yes

No

Change increment after creation

Yes

No

Choose identity for a simple per-table surrogate key. Choose a sequence when several tables share one series, numbers must cycle from 1 to 10, values must follow another sort order, or batches must reserve ranges. Sequences live outside transactions and do not enforce uniqueness by themselves, so add a unique constraint where needed and expect gaps after rollbacks.

-- Create sequence
CREATE SEQUENCE OrderNumber 
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    NO CYCLE;

-- Use sequence in INSERT with NEXT VALUE FOR function
INSERT INTO Order (OrderID, CustomerID, OrderNumber, OrderDate)
VALUES (1, 100, NEXT VALUE FOR OrderNumber, GETDATE());

-- Get next value before INSERT
DECLARE @NextOrderNum INT = NEXT VALUE FOR OrderNumber;
SELECT @NextOrderNum;

-- Get multiple sequence numbers at once for batch processing
DECLARE @FirstSeq INT, @LastSeq INT;
EXEC sp_sequence_get_range 
    @sequence_name = N'OrderNumber',
    @range_size = 100,
    @range_first_value = @FirstSeq OUTPUT,
    @range_last_value = @LastSeq OUTPUT;

-- Reset sequence
ALTER SEQUENCE OrderNumber RESTART WITH 1000;

Watch the video

12. A retailer added app-level email checks yet still got duplicate customer emails from bulk imports. Why would a database UNIQUE constraint have prevented the problem?
  • They run faster because applications cache validation rules
  • They are enforced by the engine on every write, even from imports or direct SQL
  • They replace the need for primary keys and indexes
  • They allow duplicate emails to improve marketing reach
Show answer and explanation
  • They are enforced by the engine on every write, even from imports or direct SQL

Constraints run inside the engine on every INSERT, UPDATE, and DELETE, so bulk imports and ad-hoc scripts cannot bypass them like app code.

13. A ticketing app needs one shared number series for events and concessions and must know the number before inserting. It should use a ____ and retrieve values with ____.

Shared numbering across tables

  • CHECK
  • FOREIGN KEY
  • Identity
  • NEXT VALUE FOR
  • Sequence
Show answer
  • A ticketing app needs one shared number series for events and concessions and must know the number before inserting. It should use a Sequence and retrieve values with NEXT VALUE FOR.
14. Sequence values consumed by a transaction that later rolls back are lost and will leave gaps, unless the sequence is defined to cycle.
  • True
  • False
Show answer and explanation
  • True

Sequence numbers are issued outside the transaction, so rolled-back numbers are lost, and uniqueness requires an explicit unique constraint.

JSON columns and native JSON type

Fixed columns suit predictable entities like orders, but catalogs, preferences, API payloads, audit snapshots, tenant extensions, and tags vary by record. Creating sparse columns or many side tables for every variant becomes unmanageable. JSON stores the variable portion alongside stable relational columns so new product types or settings can appear without schema changes.

  • Per-user preferences that evolve with features

  • External API responses with nested, changing shapes

  • Before-and-after audit images that follow schema evolution

  • Tenant-specific custom fields in multi-tenant apps

  • Flexible tags and metadata that differ per row

SQL Server 2025 adds a native JSON type that keeps documents in parsed binary form for faster reads, in-place updates, and better compression. Earlier versions store JSON as NVARCHAR(MAX). Reads use JSON_VALUE for scalars and JSON_QUERY for objects or arrays. Because a JSON value cannot directly serve as an index key, frequent paths are exposed through computed columns that are then indexed, and required members can be enforced with JSON_PATH_EXISTS in a check.

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

Keep stable, uniformly typed fields in relational columns for validation and direct indexing, reserving JSON for the varying part. A metadata side table can reference its parent product, require a weight member, and query nested dimensions with path expressions.

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;
  • Use JSON for semi-structured variation, not for uniform schemas

  • Index hot paths via computed columns

  • Validate required members with JSON_PATH_EXISTS checks

  • Keep predictable data in typed columns and flexible detail in JSON

Watch the video

15. A settings table stores {"theme":"dark","language":"en"}. Which functions extract the scalar theme versus the entire JSON object?
  • JSON_VALUE for both, because it handles objects and scalars identically
  • JSON_VALUE for theme and JSON_QUERY for the full object
  • JSON_QUERY for both, because scalars require JSON_QUERY
  • Neither function, because JSON must be parsed in the app
Show answer and explanation
  • JSON_VALUE for theme and JSON_QUERY for the full object

JSON_VALUE returns a scalar such as theme, while JSON_QUERY returns an object or array such as the whole document.

16. To speed frequent filtering on a JSON theme property stored in a native JSON column, a computed column using JSON_VALUE can be created and then indexed.
  • True
  • False
Show answer and explanation
  • True

The native JSON type cannot be an index key, so a computed column extracts the path and that column is indexed.

Table and index partitioning

Partitioning splits a large logical table into physical segments that can be maintained, archived, or scanned independently. A poor key can make performance worse than no partitioning, and rebuilding multi-terabyte boundaries later causes long outages, so the key and alignment should be fixed during design.

Three parts cooperate: a partition function sets boundary values, a partition scheme maps partitions to filegroups, and the partitioning column routes each row. A financial table of 1.2 TB filtered by date in 90% of queries, for example, can improve 10 to 20 times by scanning one month instead of 120, cut index rebuilds from hours to minutes per partition, switch old months to archives in seconds, and move cold data to cheaper storage for sizable savings.

  • Elimination — filters on the key read only relevant partitions

  • Parallelism — partitions can be processed across cores with per-partition statistics

  • Maintenance — rebuild or reorganize current partitions while history stays online

  • Archival — switch partitions out by metadata instead of long deletes

  • Tiering — place older partitions on slower, cheaper filegroups

Scenario

Use partitioning?

Why

80%+ queries filter by date or region

Yes

Elimination limits I/O to needed partitions

Regular monthly or quarterly archival

Yes

Switch-out completes in seconds

Rebuilds needed only for recent data

Yes

Older partitions stay available

Multi-TB tables needing tiered storage

Yes

Cold partitions move cheaply

Most queries scan all data or vary filters

No

All partitions are touched with extra overhead

Frequent single-row lookups

No

Overhead without benefit

No column matches query patterns

No

No effective key exists

Quarterly date boundaries create five partitions for a year plus prior history. Using one filegroup is simplest; multiple filegroups suit tiering or independent backups. The partition column should appear in the primary key to keep the clustered index aligned.

-- Create partition function based on date ranges
-- Use RANGE RIGHT for datetime columns to keep same-day values together
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME2)
    AS RANGE RIGHT FOR VALUES 
    ('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');

-- Create partition scheme mapping to a single filegroup (recommended)
-- Use multiple filegroups only for tiered storage or independent backups
CREATE PARTITION SCHEME PS_OrderDate
    AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);

-- Create partitioned table
-- Include partition column in primary key for clustered index alignment
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON PS_OrderDate(OrderDate);

Range keys and categorical keys

Choose a key present in most filters, evenly distributed, matching purge patterns, and stable after insert. Date ranges dominate time-series work: daily suits high volume with short retention, weekly suits medium volume, monthly is the common default, quarterly suits lower volume, and yearly suits archives. Numeric ranges such as invoice blocks work similarly. Categorical values like region can also partition by sort order for geographic or tenant isolation.

Pattern

When to Use

Daily

High-volume systems, short retention

Weekly

Medium volume, 6-12 month retention

Monthly

Most common, balances count and size

Quarterly

Lower volume, multi-year retention

Yearly

Archive scenarios, long history

-- RANGE RIGHT creates 5 partitions: <100000, 100000-199999, 200000-299999, 300000-399999, >=400000
CREATE PARTITION FUNCTION PF_InvoiceNumber (INT)
    AS RANGE RIGHT FOR VALUES 
    (100000, 200000, 300000, 400000);

Aligned indexes and ongoing management

Aligned indexes share the table scheme so each index partition matches a data partition, enabling switch-out and per-partition rebuilds. Nonaligned indexes use a different scheme or none, block switching, and are unsupported beyond 1,000 partitions. Nonclustered indexes inherit the table scheme by default.

-- Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer
    ON Orders(CustomerID)
    ON PS_OrderDate(OrderDate);

-- Create partitioned columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SalesData_CS
    ON SalesData(Revenue, Region)
    ON PS_SalesDate(SaleDate);

Inspect distribution with $PARTITION, add future bounds with SPLIT RANGE, and remove old bounds with MERGE RANGE during archival. Automate boundary creation, avoid thousands of tiny partitions, and monitor for skew where one partition holds most rows.

-- View partition information
SELECT 
    $PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber,
    MIN(OrderDate) AS MinDate,
    MAX(OrderDate) AS MaxDate,
    COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.PF_OrderDate(OrderDate)
ORDER BY PartitionNumber;
-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
    SPLIT RANGE ('2024-11-01');
-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
    MERGE RANGE ('2023-12-31');

Watch the video

An e-commerce Orders table holds several years of data. Most reports filter by OrderDate, old months must be archived quickly, and only the current month needs frequent index rebuilds.

17. Order the steps to create a partitioned Orders table that supports date elimination and aligned maintenance.
  • Create a partition function with date boundaries
  • Create a partition scheme mapping partitions to filegroups
  • Create the table on the scheme with the date in the primary key
Show answer
  1. Create a partition function with date boundaries
  2. Create a partition scheme mapping partitions to filegroups
  3. Create the table on the scheme with the date in the primary key
18. The Orders table is partitioned by OrderDate and old months must be switched to an archive in seconds. Should its CustomerID index be aligned or nonaligned, and why?
  • Nonaligned, because different partitioning speeds up switching
  • Aligned, because matching partitions enable switching and per-partition rebuilds
  • Nonaligned, because it inherits the table scheme automatically
  • Unpartitioned, because partition elimination works without indexes
Show answer and explanation
  • Aligned, because matching partitions enable switching and per-partition rebuilds

Aligned indexes share the same function so partitions switch together and can be rebuilt independently; nonaligned indexes prevent switching.

19. A table where most queries scan all rows or filter on many different columns will still benefit substantially from partitioning on any column.
  • True
  • False
Show answer and explanation
  • False

When queries scan everything or filter on many different columns, partitioning adds overhead and can perform worse than an unpartitioned table.

Extra questions

Contoso must persist vehicle telemetry in a way that stays efficient as volumes grow and old data is removed.

  • Telemetry rows must live in a partitioned table.
  • Loading fresh readings and discarding aged readings must have steady, predictable cost.
  • Predicates on the latitude, longitude and accuracy members inside the location JSON must be satisfied with an index seek.

Judge the three claims below against the proposed definition.

CREATE TABLE dbo.VehicleTelemetry
(
    TelemetryId BIGINT IDENTITY(1,1) NOT NULL,
    VehicleId NVARCHAR(50) NOT NULL,
    TelemetryTimeUtc DATETIME2(3) NOT NULL,
    BatteryPercent TINYINT NULL,
    SpeedKmh SMALLINT NULL,
    LocationJson JSON NULL,
    ErrorCodesJson JSON NULL,
    RawPayload NVARCHAR(MAX) NULL,
    SysStartTime DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
    CONSTRAINT PK_VehicleTelemetry PRIMARY KEY CLUSTERED (TelemetryId)
)
WITH
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.VehicleTelemetryHistory
    )
);
GO
CREATE INDEX IX_VehicleTelemetry_Time ON dbo.VehicleTelemetry (TelemetryTimeUtc);
CREATE JSON INDEX JI_VehicleTelemetry_Location ON dbo.VehicleTelemetry (LocationJson) FOR
(
    '$.location.latitude', '$.location.longitude',
    '$.location.accuracy'
);
20. The proposed definition satisfies the partitioning demands for telemetry.
  • True
  • False
Show answer and explanation
  • False

No partition function, partition scheme or ON-scheme placement is created, so the table has one partition. The nonclustered index on TelemetryTimeUtc can speed time filtering but does not enable partition switching or truncation for retention. True partitioning needs a partition function, a partition scheme and the clustered index built on that scheme over a partitioning column such as TelemetryTimeUtc.

21. The proposed definition satisfies the JSON property lookup demands.
  • True
  • False
Show answer and explanation
  • True

LocationJson uses the native json type and the table has the clustered primary key a JSON index requires. The JSON index explicitly lists $.location.latitude, $.location.longitude and $.location.accuracy, exactly the three required properties, so JSON_VALUE predicates on those paths can be resolved through the index instead of parsing every document.

22. A filter on $.location.heading can use the JI_VehicleTelemetry_Location index.
  • True
  • False
Show answer and explanation
  • False

A JSON index with a FOR list holds only the named paths and anything below them. The heading path is a sibling of the indexed paths, not a child of any of them, so it is absent from JI_VehicleTelemetry_Location and cannot use it. Covering every member under location would require indexing $.location itself, since indexed paths may not overlap.

An Azure SQL database keeps sales history in Sales with SalesDate, ProductKey and TotalSale, holding 10 TB loaded nightly in bulk. Typical reports scan wide ranges, filter by SalesDate or ProductKey and aggregate TotalSale with SUM(). A compact Products table serves keyed lookups and joins to Sales, and the design must keep extra storage minimal.

23. Which index should you suggest for each table to speed reporting queries? Sales: ____ Products: ____

Drag the right index kind to each table.

  • A clustered columnstore index
  • A clustered rowstore index
  • A nonclustered columnstore index
  • A nonclustered rowstore index
Show answer
  • Which index should you suggest for each table to speed reporting queries? Sales: A clustered columnstore index Products: A clustered rowstore index

An Azure SQL database holds Table1 with 25,000,000 rows and a datetime2 DateKey column covering 2020 through 2021. You must split the rows by calendar year while keeping rebuild and reindex duration as short as possible.

24. How should you finish the code? CREATE PARTITION FUNCTION PartitionByYear (datetime2) AS ____ FOR VALUES ( ____ );

Select the correct fragment for each gap.

  • '2019-01-01 00:00:00', '2020-01-01 00:00:00', '2021-12-31 23:59:59'
  • '2019-12-31 00:00:00', '2020-12-31 23:59:59'
  • '2020-01-01 00:00:00', '2020-02-01 00:00:00', '2020-03-01 00:00:00'
  • '2020-01-01 00:00:00', '2021-01-01 00:00:00'
  • PARTITION
  • RANGE LEFT
  • RANGE RIGHT
Show answer
  • How should you finish the code? CREATE PARTITION FUNCTION PartitionByYear (datetime2) AS RANGE RIGHT FOR VALUES ( '2020-01-01 00:00:00', '2021-01-01 00:00:00' );

An Azure SQL database named SalesDB supports online retail through dbo.Orders, which has a clustered index on OrderId. The table receives steady OLTP inserts and updates in business hours, while hourly scans over recent dates compute revenue trends.

  • Keep near-real-time analytical access to dbo.Orders without hurting OLTP throughput.
  • Cut down read time when fetching analytical data from dbo.Orders.
  • Allow an index that stores only rows satisfying a predicate, for example Active = 1.
25. Support NRT analytics on dbo.Orders: ____ Reduce read time when fetching analytical data: ____ Index solely rows satisfying a predicate: ____

Pick the suitable index kind for every need.

  • A clustered columnstore index
  • A clustered rowstore index ordered by key columns
  • A filtered nonclustered index with a WHERE predicate
  • A nonclustered columnstore index on an existing rowstore table
Show answer
  • Support NRT analytics on dbo.Orders: A nonclustered columnstore index on an existing rowstore table Reduce read time when fetching analytical data: A nonclustered columnstore index on an existing rowstore table Index solely rows satisfying a predicate: A filtered nonclustered index with a WHERE predicate

Database DB1 holds a nonclustered index called index1. Queries touching it are slow and contention is suspected. The kinds of operations hitting the index plus how frequently they encounter row-lock, page-lock and page-latch waits must be discovered.

26. Which dynamic management view exposes that operational and wait information?
  • sys.dm_exec_query_plan_stats
  • sys.dm_db_index_physical_stats
  • sys.dm_db_index_operational_stats
  • sys.dm_db_index_usage_stats
Show answer and explanation
  • sys.dm_db_index_operational_stats

sys.dm_db_index_operational_stats reports access counts such as inserts, updates, deletes, scans and lookups together with row_lock, page_lock, page_latch and page I/O latch wait counts and durations. Index usage statistics lack wait data, physical statistics describe size and fragmentation, and query plan statistics return cached plans rather than index-level counters.

An Azure SQL database holds a table named table1 that is stored with partitioned columnstores. The table has to be set up to satisfy all of the conditions below.

  • Every partition has to be held in compressed form.
  • The amount of compression has to be as high as possible.
  • The compressed data has to remain searchable through an index.
27. Which compression method should be applied to table1?
  • page compression
  • columnstore compression
  • GZIP compression
  • columnstore archival compression
Show answer and explanation
  • columnstore archival compression

Columnstore archival compression layers the XPRESS method over regular columnstore storage, giving the greatest space saving, and it can be applied per partition while leaving the data inside the columnstore index and therefore queryable. Ordinary columnstore compression is already present on every columnstore and cannot be tuned to increase the ratio. Page compression belongs to rowstore tables and compresses less effectively. Values produced by GZIP COMPRESS become varbinary and cannot be indexed.

On an Azure SQL logical server the following script is executed, with the table creation running inside the Sales database.

CREATE DATABASE Sales
GO
CREATE TABLE [dbo].[Orders]
  (
    [OrderID] INT NOT NULL,
    [OrderDescription] NVARCHAR (MAX) NOT NULL,
    [Timestamp] Datetime2 NOT NULL
  )
  WITH (
    SYSTEM_VERSIONING = ON,
    LEDGER = ON
  );
GO
28. Only insertion of rows is permitted on the Orders table.
  • True
  • False
Show answer and explanation
  • False

Without APPEND_ONLY = ON the statement produces an updatable ledger table, which permits inserts, updates and deletes while preserving prior versions in history. Only an append-only ledger table is restricted to inserts.

29. Every extra table placed in the Sales database must be built with LEDGER = ON.
  • True
  • False
Show answer and explanation
  • False

Sales was created without WITH LEDGER = ON, so it is an ordinary database able to hold both ledger and non-ledger tables. The LEDGER = ON restriction on every table applies solely inside a ledger database.

30. A GENERATED ALWAYS column has to be added to the script to stamp each Orders row with a time.
  • True
  • False
Show answer and explanation
  • False

When the GENERATED ALWAYS columns are omitted, the engine adds ledger transaction and sequence columns automatically, and commit time is taken from sys.database_ledger_transactions. The Timestamp column in the script is a regular column filled by the application, not a system timestamp.

For every table in database DB1 the fragmentation details of its data and indexes have to be retrieved.

31. Which command should be executed?
  • sys.dm_db_index_usage_stats
  • DBCC CHECKALLOC
  • DBCC SHOWCONTIG
  • sys.dm_db_index_physical_stats
Show answer and explanation
  • sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats returns size and fragmentation per index level and partition, including avg_fragmentation_in_percent used to choose between reorganize and rebuild. DBCC SHOWCONTIG is deprecated and unavailable in Azure SQL Database, DBCC CHECKALLOC checks allocation consistency, and index usage statistics only count seeks, scans and updates.

Retail outlets each keep a sales database. At headquarters you maintain a summary with StoreID, ProductID, Qtysold, Totprodsales, and Datesold fed each evening. Every row must represent one store, one product, and one day total, with no repeated combinations.

32. What should your solution include?
  • Create a unique constraint
  • Create a foreign key to the StoreID column in each store table
  • Create a rule bound to the StoreID column
  • Create a check constraint
  • Create a table-valued user-defined function
Show answer and explanation
  • Create a unique constraint

A UNIQUE constraint rejects any insert duplicating the covered columns, so defining it over StoreID, ProductID, and Datesold guarantees one row per store, product, and day. A foreign key only checks existence in another table within the same database, while rules and CHECK constraints test single-row conditions rather than duplicates across rows, and a table-valued function only returns data when queried.

The following statements are run against database DB1 to create a table and rebuild it with row compression.

CREATE TABLE table1 (
  column1 TINYINT,
  column2 VARCHAR(255),
  column3 NCHAR(10),
  column4 BINARY(50),
  column5 DATETIME2
);

ALTER TABLE table1 REBUILD WITH (DATA_COMPRESSION = ROW);
33. Every value in column3 will take up 10 bytes of disk space.
  • True
  • False
Show answer and explanation
  • False

Row compression stores fixed-width character values in variable-length form with Unicode compression and strips padding, so each nchar(10) value occupies a size that varies with its content rather than a constant 10 bytes.

34. Dictionary compression will shrink table1 on disk.
  • True
  • False
Show answer and explanation
  • False

Dictionary compression is applied only as part of page compression. Rebuilding with DATA_COMPRESSION = ROW applies solely the row-level storage format.

35. Row compression lessens the storage consumed by the types held in column1, column2 and column4.
  • True
  • False
Show answer and explanation
  • False

Tinyint already uses one byte and varchar is already variable length, so row compression does not reduce column1 or column2, even though it can affect binary and datetime2 values.

An Azure SQL database named DB1 needs a new partitioned table. Three objects have to be built in the correct creation sequence.

36. The object to build first is ____, the object to build second is ____, and the object to build third is ____.

Partitioned table creation order

  • a filegroup
  • a partition function
  • a partition scheme
  • a table
  • an aligned index
Show answer
  • The object to build first is a partition function, the object to build second is a partition scheme, and the object to build third is a table.
37. A transaction table is partitioned by year. Updating records escalates to an exclusive lock on the whole table, which causes occasional slowdowns for users. What should you configure to avoid exclusive table locks while keeping data integrity?
  • Configure LOCK_ESCALATION to DISABLE.
  • Configure DATA_COMPRESSION to ROW at the partition level.
  • Configure LOCK_ESCALATION to AUTO.
  • Configure LOCK_ESCALATION to TABLE.
  • Configure DATA_COMPRESSION to PAGE at the partition level.
Show answer and explanation
  • Configure LOCK_ESCALATION to AUTO.

Exclusive table locks come from lock escalation: once a statement holds at least 5,000 row or page locks, the engine collapses them into one table lock. With LOCK_ESCALATION set to TABLE, the default, escalation always targets the whole table even when it is partitioned. With AUTO, a partitioned table escalates to the HoBT level, meaning the partition, and a lock escalated to a partition is never escalated further to the table, so updating one year locks at most that partition while integrity locks remain. DISABLE avoids escalation in most cases but does not fully forbid table locks, for example for a serializable scan of a heap, and forces many individual row locks to stay in memory. DATA_COMPRESSION at ROW or PAGE level only compresses stored data and does not affect lock granularity.

You plan to locate indexes that are never read so they can be dropped from the database.

38. Querying sys.dm_db_index_operational_stats identifies the unused indexes.
  • True
  • False
Show answer and explanation
  • False

sys.dm_db_index_operational_stats reports low-level I/O, locking, latching, and access-method activity per partition. Its counters live only while the heap or B-tree metadata object stays in cache and reset when that metadata is reloaded, which can happen repeatedly for a rarely used index under memory pressure. It therefore cannot prove whether an index was used or when it was last used.

39. Querying sys.dm_db_index_usage_stats identifies the unused indexes.
  • True
  • False
Show answer and explanation
  • True

sys.dm_db_index_usage_stats reports seeks, scans, lookups, and updates per index plus when each operation last occurred. An index with no entry, or with updates but no seeks, scans, or lookups, is maintained on writes but never used for reads and is a candidate for removal. Counters reset when the engine restarts, so they should cover a representative workload period.

40. Querying sys.dm_db_index_physical_stats identifies the unused indexes.
  • True
  • False
Show answer and explanation
  • False

sys.dm_db_index_physical_stats reports size and fragmentation, such as page counts and average fragmentation per level, which guides reorganize or rebuild decisions. It records nothing about how often queries read the index, so it cannot reveal which indexes are unused.

An ETL job cleans and merges incoming feeds using a reference table that flags rows for cleansing in the target. The job rebuilds that reference table on every execution, and the hosting server reboots daily. You must cut both query time and reference-table load time.

41. What should you do?
  • Change the target table to memory-optimized and cleanse with a natively compiled procedure
  • Change the reference table to memory-optimized with DURABILITY set to SCHEMA_AND_DATA
  • Build a natively compiled procedure handling both tables
  • Change the reference table to memory-optimized with DURABILITY set to SCHEMA_ONLY
Show answer and explanation
  • Change the reference table to memory-optimized with DURABILITY set to SCHEMA_ONLY

A staging reference rebuilt each run suits a non-durable memory-optimized table: rows reside in memory for fast lookups and SCHEMA_ONLY skips transaction logging, so loads avoid log I/O. After a restart only data disappears while schema remains, and the next ETL recreates it. SCHEMA_AND_DATA would persist and log every insert needlessly, converting the target does not speed the reference load, and native compilation cannot span disk tables or remove load I/O.

42. An Azure SQL database holds a rarely read table with a columnstore index. You apply columnstore archival compression. Which two outcomes can result?
  • Queries against the index will perform more disk I/O
  • Queries against the index will fetch fewer data pages
  • The index will occupy more disk space
  • The index will occupy more memory
  • Queries against the index will use more CPU
Show answer and explanation
  • Queries against the index will fetch fewer data pages
  • Queries against the index will use more CPU

Columnstore archival compression applies an extra XPRESS layer over standard columnstore compression. The index becomes smaller, so scans fetch fewer pages, but each read must decompress that extra layer, raising CPU cost and elapsed time. That storage-for-CPU exchange fits rarely queried data. Disk I/O and disk footprint shrink rather than grow, and the penalty appears as CPU, not additional memory.

A database holds an Employees table with company staff details. Every proposal must be judged against the same business rules.

  • Restrict the values permitted in the Salary column.
  • Reject salaries below 15000 and above 300000.
  • Derive allowed values from a logical expression.
  • Skip integrity checks for DELETE statements.
43. Proposal: enforce the rules with a check constraint on the table. Does this satisfy the goal?
  • True
  • False
Show answer and explanation
  • True

A CHECK constraint enforces domain rules with a Boolean expression, for example Salary between 15000 and 300000, and is tested on INSERT and UPDATE only, never on DELETE, which matches all four rules.

44. Proposal: enforce the rules with a FOR UPDATE trigger on the table. Does this satisfy the goal?
  • True
  • False
Show answer and explanation
  • False

A FOR UPDATE trigger is equivalent to AFTER UPDATE and fires only for updates, never for inserts, so out-of-range salaries could still enter through new rows. A declarative CHECK covers both inserts and updates from a logical expression.

45. Proposal: enforce the rules with cascading referential integrity constraints. Does this satisfy the goal?
  • True
  • False
Show answer and explanation
  • False

Cascading referential actions dictate what happens to dependent rows when a referenced key is deleted or updated. They do not bound a numeric column to a range and they operate exactly on deletes, contrary to the requirement to skip delete validation.

46. Proposal: enforce the rules with a foreign key on the table. Does this satisfy the goal?
  • True
  • False
Show answer and explanation
  • False

A foreign key draws its permitted values from another table rather than from a logical expression, so it cannot express a salary range. It is also checked on deletes from the referenced side, which conflicts with the demand for no delete validation.

An Azure SQL database named SQLDb1 holds the objects listed below. The text column stores JSON documents and you must shrink its footprint as much as possible.

NameType
Table1Rowstore table
Column1Nvarchar(max)
47. What should you use to compress Column1?
  • the COMPRESS() function
  • columnstore archive compression
  • row compression
  • columnstore compression
Show answer and explanation
  • the COMPRESS() function

COMPRESS applies GZIP to an expression and returns varbinary(max), so JSON text with repeated keys compresses strongly while the table can remain rowstore. Values are restored with DECOMPRESS and cannot be indexed or queried with JSON functions until expanded. Row compression leaves nvarchar(max) untouched, and both columnstore variants require a columnstore structure; a nonclustered columnstore cannot include nvarchar(max) and large values would remain off-row in LOB storage anyway.

A Microsoft Fabric SQL database holds a table called WebSite.Logs used for application telemetry. Its log column, defined as nvarchar(max), holds JSON documents. A daily report filters rows on the $.severity property inside that JSON and returns the LogId, LogDateTime and log columns, but it often scans the whole table. The table must be changed so filtering on that JSON property can seek efficiently and the columns returned by the report can be satisfied without returning to the base table.

ALTER TABLE WebSite.Logs
ADD severity [Target 1];
GO
CREATE INDEX ix_severity
ON WebSite.Logs(severity)
[Target 2];
GO
48. Target 1, which defines the new severity column, should be ____, while Target 2, which finishes the index definition, should be ____.

Complete the Transact-SQL code.

  • AS JSON_QUERY([log], '$.severity')
  • AS JSON_VALUE([log], '$.severity') PERSISTED
  • INCLUDE (log)
  • INCLUDE (LogId, LogDateTime, [log])
Show answer
  • Target 1, which defines the new severity column, should be AS JSON_VALUE([log], '$.severity') PERSISTED, while Target 2, which finishes the index definition, should be INCLUDE (LogId, LogDateTime, [log]).
Page 1 of 14Views, Stored Procedures, Functions and Triggers