OverviewPracticeGlossary

Practice

Pick what to practice and how — your progress is saved as you go.

These are the questions on the free pages, and a free account lets you practice them. The full book has 303 more.

  • Part 1: Database Objects, Advanced T-SQL and AI-Assisted Development 4 of 6 pages
  • Part 2: Security, Performance Tuning and CI/CD Deployment 4 pages
  • Part 3: AI in SQL: Embeddings, Vector Search and RAG 4 pages

Missed questions

0 questions you last got wrong

Saved for later

0 questions you saved while practicing

What do you want to practice?

These are the questions on the free pages. Test yourself below, and tick the pages to include in a practice session.

Filter
    • 1. 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.

      2. 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
      3. 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.

      4. 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.

      5. 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.

      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.

      6. 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
      7. 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.

      8. 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.

      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'
      );
      9. 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.

      10. 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.

      11. 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.

      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.

      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.

      15. 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.

      16. 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.

      17. 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.

      18. 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.

      19. 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.

      20. 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.

      21. 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.

      22. 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.

      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]).
    • The MaintenanceEvents table records MaintenanceId as key plus VehicleId, LastModifiedUtc and a description. Every alteration of a row must stamp LastModifiedUtc with the moment of the alteration, and the stamping logic must never trigger itself repeatedly.

      1. Complete the trigger so changed rows are stamped without recursion: CREATE TRIGGER dbo.trgMaintenanceEvents_UpdateTimestamp ON dbo.MaintenanceEvents AFTER UPDATE AS BEGIN IF ____ RETURN; UPDATE m SET LastModifiedUtc = SYSUTCDATETIME() FROM dbo.MaintenanceEvents m INNER JOIN inserted i ON ____; END; GO

      Maintenance timestamp trigger

      • i.MaintenanceId IS NOT NULL
      • m.LastModifiedUtc <> i.LastModifiedUtc
      • m.MaintenanceId = i.MaintenanceId
      • m.VehicleId = i.VehicleId
      • TRIGGER_NESTLEVEL(@@PROCID) > 1
      Show answer
      • Complete the trigger so changed rows are stamped without recursion: CREATE TRIGGER dbo.trgMaintenanceEvents_UpdateTimestamp ON dbo.MaintenanceEvents AFTER UPDATE AS BEGIN IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN; UPDATE m SET LastModifiedUtc = SYSUTCDATETIME() FROM dbo.MaintenanceEvents m INNER JOIN inserted i ON m.MaintenanceId = i.MaintenanceId; END; GO
      2. A retail database executes the same order-insert logic hundreds of times per hour. Why does placing that logic in a stored procedure typically outperform sending ad-hoc SQL each time?
      • They store result rows permanently like materialized tables
      • They automatically hide sensitive columns without permissions
      • They reuse a cached execution plan instead of optimizing every call
      • They can be joined directly inside a SELECT statement
      Show answer and explanation
      • They reuse a cached execution plan instead of optimizing every call

      Caching avoids repeated parsing and optimization, so repeated calls reuse the compiled plan and run with less overhead.

      3. User stored procedures named with the sp_ prefix incur extra lookup overhead and should be avoided.
      • True
      • False
      Show answer and explanation
      • True

      The prefix is reserved for system procedures; using it forces an extra lookup in master and can cause name conflicts.

      4. Order the steps in a safe InsertCustomerOrder procedure that validates, writes, and handles errors transactionally.
      • Begin a transaction and insert the order
      • Commit the transaction on success
      • Roll back and report the error on failure
      • Validate the customer exists
      Show answer
      1. Validate the customer exists
      2. Begin a transaction and insert the order
      3. Commit the transaction on success
      4. Roll back and report the error on failure
      5. In T-SQL, what keyword marks a stored-procedure parameter that returns a value to the caller?
      Show answer and explanation
      • OUTPUT, output parameter

      Output parameters are declared and passed with the OUTPUT keyword so the procedure can assign a value back to the caller variable.

      6. A campus database needs a reusable tax calculation that can appear inside many different SELECT and WHERE expressions. Why is a scalar function suitable for that tax logic?
      • Only inside an EXECUTE statement with temp tables
      • Directly in SELECT lists and WHERE clauses as an expression
      • Only as an automatic response to INSERT or DELETE
      • Only to define a virtual table without parameters
      Show answer and explanation
      • Directly in SELECT lists and WHERE clauses as an expression

      Scalars return one value and are designed to sit inside expressions, unlike procedures that must be executed separately.

      7. A scalar function that calls GETDATE() is non-deterministic and cannot be used in an indexed view.
      • True
      • False
      Show answer and explanation
      • True

      System date calls make output vary even for identical inputs, blocking use in indexed views and indexed computed columns.

      8. In CREATE FUNCTION, the ____ clause declares the data type of the single value the routine will send back.

      Function definition clause

      • OUTPUT
      • RETURNS
      • TRANSACTION
      Show answer
      • In CREATE FUNCTION, the RETURNS clause declares the data type of the single value the routine will send back.

      An online store repeats the same customer-order join in five reports and wants to hide customer phone numbers from support staff while keeping the logic in one place.

      9. A university bookstore runs the same three-table join for orders in many applications and must hide payment columns from some roles. The join needs no parameters. Which object best centralizes this read logic?
      • A stored procedure, because procedures are the only object that can join tables
      • A view, because it encapsulates the join and can limit visible columns
      • A DDL trigger, because triggers rewrite queries automatically
      • A scalar function, because it returns a result set for joins
      Show answer and explanation
      • A view, because it encapsulates the join and can limit visible columns

      A view stores a SELECT that joins base tables and can expose only safe columns, so callers query one object without learning the join or seeing hidden columns.

      10. A SQL Server view physically stores a copy of its result rows at creation time.
      • True
      • False
      Show answer and explanation
      • False

      Views hold only the SELECT definition; data stays in base tables and is retrieved each time the view is queried.

      11. A view filtered to orders above 1000 uses ____ so an insert of 500 through that view is rejected.

      View write protection

      • WITH CHECK OPTION
      • WITH ENCRYPTION
      • WITH PLAN CACHING
      Show answer
      • A view filtered to orders above 1000 uses WITH CHECK OPTION so an insert of 500 through that view is rejected.
      12. Inventory must decrease after order lines are inserted, while updates to a complex order view must be redirected to base tables. How do the two DML trigger timings differ for these needs?
      • Both replace the triggering statement; AFTER is only faster
      • AFTER runs following the modification, INSTEAD OF executes in place of it
      • AFTER handles schema events, INSTEAD OF handles SELECT queries
      • AFTER accepts parameters, INSTEAD OF returns result sets
      Show answer and explanation
      • AFTER runs following the modification, INSTEAD OF executes in place of it

      AFTER runs after the row change for cascading or logging, while INSTEAD OF replaces the change to implement custom update logic.

      13. An UPDATE trigger sees new row versions in ____ and old row versions in ____.

      Trigger pseudo-tables

      • deleted
      • inserted
      • modified
      • updated
      Show answer
      • An UPDATE trigger sees new row versions in inserted and old row versions in deleted.
      14. A DML trigger executes automatically whenever its defined data-modification event occurs.
      • True
      • False
      Show answer and explanation
      • True

      Once created for INSERT, UPDATE or DELETE, the routine launches without an explicit EXEC call on every matching event.

      A university system has four needs: a five-table enrollment join for reports, an enrollment transaction that checks seats then inserts rows, a tuition calculation reused in queries, and an automatic log of grade changes.

      15. Match each university database need to the most appropriate programmability object.
      • Simplify a 5-table join for many reports
      • Validate seats, insert enrollment, update counts atomically
      • Compute tuition from credits and residency in SELECTs
      • Record every grade update transparently
      • Scalar function
      • Stored procedure
      • Trigger
      • View
      Show answer and explanation
      • Simplify a 5-table join for many reports: View
      • Validate seats, insert enrollment, update counts atomically: Stored procedure
      • Compute tuition from credits and residency in SELECTs: Scalar function
      • Record every grade update transparently: Trigger

      Views encapsulate parameter-free joins for repeated reporting.

      Procedures bundle validated multi-table writes in one transaction.

      Scalar functions provide reusable math inside queries.

      Triggers fire without app calls to record every change.

      16. A query filters a million-row enrollment table with a scalar function in its WHERE clause and runs slowly. Which redesign best addresses the per-row execution cost?
      • Nest views three levels deep to hide the calculation
      • Move the calculation into an AFTER trigger on the large table
      • Replace the scalar filter with an inline table-valued function or set-based logic
      • Add WITH CHECK OPTION to the scalar function
      Show answer and explanation
      • Replace the scalar filter with an inline table-valued function or set-based logic

      Scalar evaluation per row on a large scan is costly; an inline table function or set-based rewrite lets the optimizer plan the whole query.

      17. Match each table-valued function style to its defining structure.
      • Holds a single SELECT with inferred table shape
      • Declares a table variable and builds it with multiple statements
      • Inline table-valued function
      • Multi-statement table-valued function
      Show answer and explanation
      • Holds a single SELECT with inferred table shape: Inline table-valued function
      • Declares a table variable and builds it with multiple statements: Multi-statement table-valued function

      A single SELECT is expanded into the outer query, aiding optimization.

      Explicit table variable plus BEGIN...END supports staged procedural assembly.

      18. A query must list every customer alongside only that customer's completed orders by passing each CustomerID into GetCustomerOrders(CustomerID). Which construct expresses that correlated call?
      • Use EXEC with temp tables to capture procedure output
      • Use an AFTER trigger on the Customers table
      • Use CROSS APPLY with the customer key as the argument
      • Use WITH CHECK OPTION on a view
      Show answer and explanation
      • Use CROSS APPLY with the customer key as the argument

      CROSS APPLY evaluates the function for each outer row, allowing column values like CustomerID to become parameters.

      Fabrikam needs the names of patients who had at least one medical procedure in the past 30 days. The logic must live in GetActivePatients and must check only for the presence of a matching row through a correlated reference to the outer patient.

      19. Complete the procedure so it returns the required names with an existence check: CREATE PROCEDURE dbo.GetActivePatients AS BEGIN SET NOCOUNT ON; SELECT p.Name FROM dbo.Patients AS p ____ ( SELECT PatientId FROM dbo.Procedures AS pr ____ AND pr.TransactionDate >= DATEADD(DAY, -30, SYSUTCDATETIME()) ); END;

      Active-patient procedure

      • HAVING p.PatientId = pr.PatientId
      • HAVING p.TransactionId = pr.TransactionId
      • WHERE EXISTS
      • WHERE NOT EXISTS
      • WHERE p.PatientId = pr.PatientId
      • WHERE p.PatientId IN
      • WHERE p.PatientId NOT IN
      • WHERE p.TransactionId = pr.TransactionId
      Show answer
      • Complete the procedure so it returns the required names with an existence check: CREATE PROCEDURE dbo.GetActivePatients AS BEGIN SET NOCOUNT ON; SELECT p.Name FROM dbo.Patients AS p WHERE EXISTS ( SELECT PatientId FROM dbo.Procedures AS pr WHERE p.PatientId = pr.PatientId AND pr.TransactionDate >= DATEADD(DAY, -30, SYSUTCDATETIME()) ); END;

      A Fabric SQL database includes a multi-statement table function Sales.mstvf_OrderStatus() that supplies order-status rows, plus a scalar routine dbo.ufn_GetTaxMultiplier (@TaxAmt money, @StateCode char(2)) that yields a numeric tax factor.

      Reporting statements often combine the table function with Sales.SalesOrderHeader and fetch bulky outputs. Reviews note that those statements generate unsteady execution plans. The next call was also seen to raise an error during inspection.

      DECLARE @ret numeric(9, 4);
      EXEC @ret = ufn_GetTaxMultiplier @TaxAmt = 100.00, @StateCode = 'WA';
      20. GETDATE() may be used inside dbo.ufn_GetTaxMultiplier to return varying values.
      • True
      • False
      Show answer and explanation
      • True

      Routines written in Transact-SQL may invoke nondeterministic system functions such as GETDATE(). Only side-effecting routines NEWID, NEWSEQUENTIALID, RAND and TEXTPTR are barred inside such functions. Relying on GETDATE() makes the result nondeterministic, which excludes it from indexed views and indexes on computed columns, but the routine itself remains legal.

      21. Recasting Sales.mstvf_OrderStatus() as an inline table function would yield steadier plans.
      • True
      • False
      Show answer and explanation
      • True

      The optimizer cannot look inside a multi-statement table function, so it falls back to a fixed guess or, with interleaved execution, to the row count from one run, regardless of later sizes. With big and shifting outputs the reused plans fit badly. An inline table function is a single SELECT folded into the outer query like a parameterized view, letting the optimizer use base-table statistics and shape plans to the real data.

      22. Qualifying the routine as dbo.ufn_GetTaxMultiplier in the EXEC call will fix the error.
      • True
      • False
      Show answer and explanation
      • True

      A scalar routine may be launched with EXECUTE into a variable, but it must be called with at least its two-part identifier. Adding the schema qualifier, as in EXEC @ret = dbo.ufn_GetTaxMultiplier @TaxAmt = 100.00, @StateCode = 'WA';, follows the documented form and cures the failure.

      An Azure SQL database needs a scalar function that counts how many year transitions have occurred between a supplied order moment and the present moment, producing one positive whole number.

      CREATE FUNCTION dbo.ufnYearsSinceOrder (@OrderDate datetime2)
      RETURNS int
      AS
      BEGIN
      
      END
      23. What statement belongs on line 05 inside the BEGIN...END block to finish the function?
      • RETURN DATEDIFF(year, GETDATE(), @OrderDate);
      • DATEDIFF(month, @orderdate, GETDATE()) / 12
      • DATEPART(year, GETDATE()) - DATEPART(year, @orderdate)
      • RETURN DATEDIFF(year, @OrderDate, GETDATE());
      Show answer and explanation
      • RETURN DATEDIFF(year, @OrderDate, GETDATE());

      DATEDIFF with the year part reports signed year boundaries crossed from the start to the end date, so placing @OrderDate first and GETDATE() second gives a positive value for past orders. A scalar function body must finish with RETURN of the declared type. Reversing the dates gives a negative value for past orders. Bare expressions without RETURN are not valid statements in a function body, and counting month boundaries divided by 12 does not equal year boundaries crossed.

      24. You are building a customer-sales application. You need an object that returns the total of finalized orders for a given order identifier so the result can be reused inside other Transact-SQL statements. What should you build?
      • extended procedure
      • CLR procedure
      • user-defined procedure
      • DML trigger
      • scalar-valued function
      • table-valued function
      Show answer and explanation
      • scalar-valued function

      A scalar function yields one value and may appear anywhere an expression is permitted, including SELECT lists, filters, and assignments, so the finalized-order total can feed other statements directly. A table-valued function yields a table for FROM, procedures must run as standalone EXECUTE and cannot embed in an expression, and triggers only respond to data modifications.

      25. You maintain a view that contains an aggregate. Changes made to the view columns must flow through to the base tables behind the view. What should you create to make updates through the view possible?
      • A nonclustered index
      • A schema-bound view
      • A stored procedure
      • An INSTEAD OF trigger
      Show answer and explanation
      • An INSTEAD OF trigger

      Columns can be changed directly through a view only when they map straight to columns of a single base table. Columns produced by an aggregate, or shaped by GROUP BY, HAVING, or DISTINCT, cannot be changed directly. An INSTEAD OF trigger on the view runs in place of the UPDATE, INSERT, or DELETE and holds logic that writes the change to the base tables, which makes the view updatable through that statement even when several base tables must be touched. SCHEMABINDING only blocks changes to the referenced objects definitions. A nonclustered index on the view can speed up reads but does not change updatability. A stored procedure can update the base tables itself, but that leaves the view non-updatable.

      26. You are building a customer-sales application. You need a Transact-SQL object that inserts into Customers, returns 0 on success and 1 on failure, and supports TRY...CATCH. What should you build?
      • extended procedure
      • CLR procedure
      • user-defined procedure
      • DML trigger
      • scalar-valued function
      • table-valued function
      Show answer and explanation
      • user-defined procedure

      A Transact-SQL stored procedure can wrap the INSERT in TRY...CATCH and hand back an integer status through RETURN, with 0 for success and 1 for failure, which the caller captures from EXEC. Extended and CLR procedures are written in external languages rather than Transact-SQL, triggers fire automatically and cannot return a value to a caller, and functions cannot modify database state or use TRY...CATCH.

      A relational warehouse holds 1 TB. A procedure named usp_sp1 builds a styled HTML snippet carrying colour and font formatting, and you must hand that snippet back to the caller.

      27. What should you do to return the HTML fragment?
      • Apply the NOLOCK hint
      • Run the DBCC UPDATEUSAGE statement
      • Adjust the max worker threads setting
      • Pass a table-valued parameter
      • Switch ALLOW_SNAPSHOT_ISOLATION to ON
      • Switch XACT_ABORT to ON
      • ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO);
      • Use OUTPUT parameters
      Show answer and explanation
      • Use OUTPUT parameters

      Procedures communicate through result sets, OUTPUT parameters, and an integer return code. RETURN can only carry an integer, while an OUTPUT parameter of a string type such as nvarchar(max) passes the variable value back on exit. Table-valued parameters are READONLY inputs, and locking, isolation, DBCC, and worker-thread settings do not transport data.

In what order?

In orderShuffledMissed first