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.2 Views, Stored Procedures, Functions and Triggers

Modern e-commerce databases must serve orders, inventory and reporting without duplicating logic in every app. SQL Server offers four programmability objects that centralize that logic inside the database: views for reusable queries, stored procedures for multi-step operations, functions for calculations and parameterized result sets, and triggers for automatic responses to change.

Watch the video

Views Simplify Data Access

A view is a saved SELECT that behaves like a virtual table. It stores no rows itself; every query against it runs the underlying statement afresh against base tables. Conceptually a user request goes to the view layer, the view invokes its stored select logic, and that logic pulls from one or more base tables such as TABLE_A and TABLE_B.

Diagram: User Query, VIEW, SELECT STATEMENT, TABLE_A, TABLE_B

Views hide JOIN complexity and reshape data for callers. A reporting app can select from one order-summary view instead of rebuilding customer-order-product joins, and security improves because you can grant access to the view while denying direct table access and exposing only safe columns and rows.

Watch the video

CREATE VIEW Sales.CustomerOrders
AS
SELECT 
    c.CustomerID,
    c.CustomerName,
    c.Email,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM Sales.Customers c
INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;

Once defined, the view is queried like a table and can include derived values. A single CASE expression for order size, for example, is then reused consistently instead of being copied into many reports.

CREATE VIEW Sales.OrderSummary
AS
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    CASE 
        WHEN TotalAmount < 100 THEN 'Small'
        WHEN TotalAmount < 1000 THEN 'Medium'
        ELSE 'Large'
    END AS OrderSize
FROM Sales.Orders;
  • List columns explicitly rather than using SELECT *, so later changes to base tables do not silently alter the view.

  • Add WITH CHECK OPTION when the view may support writes, to reject inserts or updates that would fall outside the view filter, such as a low-value order added through a high-value view.

  • Keep each view focused on one purpose instead of building a large multi-purpose view that is hard to tune and understand.

CREATE VIEW Sales.HighValueOrders
AS
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM Sales.Orders
WHERE TotalAmount > 1000
WITH CHECK OPTION;

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.

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

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

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

Stored Procedures Encapsulate Operations

A stored procedure is a named, compiled batch of T-SQL saved on the server and run as a unit. It can validate inputs, touch many tables, log activity and enforce rules, so applications call controlled logic rather than issuing direct table writes.

Watch the video

Performance comes largely from plan reuse. An ad-hoc statement is parsed and optimized on each submission, while a procedure caches its execution plan after first use and reuses it. Adding SET NOCOUNT ON and wrapping the body in BEGIN...END further cuts chatter and clarifies scope.

CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END

Input parameters with @name type make procedures flexible, with NULL defaults creating optional filters. OUTPUT parameters return scalar values to the caller, which must supply a variable and the OUTPUT keyword when executing.

CREATE PROCEDURE dbo.GetCustomerOrdersByDate
    @CustomerID int,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
        AND (@StartDate IS NULL OR OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR OrderDate <= @EndDate)
    ORDER BY OrderDate DESC;
END
CREATE PROCEDURE dbo.CalculateOrderTotal
    @OrderID int,
    @TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;

    RETURN 0;
END

Reliable procedures trap failures with TRY...CATCH, often inside an explicit transaction. The CATCH block checks @@TRANCOUNT before rolling back, then surfaces details via ERROR_MESSAGE(), ERROR_SEVERITY() and ERROR_STATE().

CREATE PROCEDURE dbo.InsertCustomerOrder
    @CustomerID int,
    @OrderDate datetime,
    @TotalAmount decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Customer does not exist.', 16, 1);
        END

        -- Insert order
        INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
        VALUES (@CustomerID, @OrderDate, @TotalAmount);

        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END
  • Qualify every object with its schema such as dbo.Orders to avoid resolution cost and ambiguity.

  • Validate parameters early and fail fast with RAISERROR when an ID is missing or out of range.

  • List needed columns explicitly instead of SELECT * to survive schema changes and reduce I/O.

  • Name procedures with an action verb like Get, Update or Calculate so intent is obvious.

  • Never use the sp_ prefix for user code, because SQL Server probes the master database first and adds overhead.

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

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

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

Scalar Functions Return Single Values

A scalar user-defined function takes zero or more inputs and yields exactly one value, so it can appear anywhere an expression is allowed, including SELECT lists and WHERE clauses. The definition fixes the return type up front and ends with a RETURN of that value.

Watch the video

CREATE FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    SET @TaxAmount = @Amount * @TaxRate
    RETURN @TaxAmount
END

Teams use scalars to hold business math in one routine. A tenure function based on DATEDIFF guarantees every query computes years of service identically, and changing the rule requires editing only the function.

CREATE FUNCTION dbo.GetEmployeeTenure
(
    @HireDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Tenure INT
    SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
    RETURN @Tenure
END
Note

Because it calls GETDATE(), the tenure example is non-deterministic and cannot back an indexed view or an index on a computed column. Passing the reference date as a parameter restores determinism where needed.

  • Favor deterministic logic that returns the same output for the same inputs, enabling more optimizations.

  • Avoid side effects such as modifying tables, since execution order and frequency are not guaranteed.

  • Watch row-by-row cost: applied to large sets in SELECT or WHERE, a scalar may run per row and hurt throughput, where an inline table-valued function often performs better.

CREATE FUNCTION dbo.FormatPhoneNumber
(
    @PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
    DECLARE @FormattedNumber VARCHAR(14)

    IF LEN(@PhoneNumber) = 10
        SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
                               SUBSTRING(@PhoneNumber, 4, 3) + '-' +
                               SUBSTRING(@PhoneNumber, 7, 4)
    ELSE
        SET @FormattedNumber = @PhoneNumber

    RETURN @FormattedNumber
END

Definitions evolve with ALTER FUNCTION, which keeps permissions and dependencies, while DROP FUNCTION IF EXISTS removes a routine safely after checking sys.sql_expression_dependencies for dependents.

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

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

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

Table-Valued Functions Return Tables

Table-valued functions package parameterized query logic that returns a row set usable wherever a table or view appears, including FROM and JOIN. Unlike procedures, their output can be composed further with filtering and aggregation.

Watch the video

  • Inline functions hold one SELECT behind a single RETURN, with the shape inferred automatically and expanded into the outer plan like a parameterized view.

  • Multi-statement functions declare a table variable explicitly and fill it with procedural steps, supporting branching and staged work at the cost of less transparent optimization.

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Callers treat the result as a source and may add their own predicates. Multi-statement variants instead define columns such as product, quantity and revenue, populate the variable with grouped joins over a date window, then RETURN it for extra procedural control.

CREATE FUNCTION dbo.GetProductSalesSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @SalesSummary TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantity INT,
    TotalRevenue DECIMAL(18,2),
    AveragePrice DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @SalesSummary
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        AVG(od.UnitPrice) AS AveragePrice
    FROM Production.Products p
    INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;

    RETURN;
END;

Composition uses CROSS APPLY to invoke the function per outer row, which is ideal when a column supplies the parameter, such as fetching each customer's completed orders. Simple inline routines can also join directly when no per-row evaluation is required.

SELECT 
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';
11. 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.

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

Triggers Automate Responses to Change

A trigger is a stored routine that fires on its own when a defined event occurs, rather than being called. DML triggers watch row changes from INSERT, UPDATE or DELETE, while DDL triggers watch schema statements such as CREATE, ALTER or DROP.

Watch the video

DML triggers come in two timings. AFTER triggers run following the modification for validation, cascading updates or logging. INSTEAD OF triggers substitute their own code for the requested change, useful for making complex views updatable or enforcing elaborate rules.

CREATE TRIGGER tr_UpdateInventory
ON Sales.OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE Inventory.Products
    SET QuantityInStock = QuantityInStock - i.Quantity
    FROM Inventory.Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END;
CREATE TRIGGER tr_UpdateOrderView
ON Sales.OrderSummaryView
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Sales.Orders
    SET OrderStatus = i.OrderStatus,
        ModifiedDate = GETDATE()
    FROM Sales.Orders o
    INNER JOIN inserted i ON o.OrderID = i.OrderID;
END;

Inside a trigger the inserted and deleted pseudo-tables expose new and old images: inserts fill inserted, deletes fill deleted, and updates fill both. The UPDATE() test detects whether a particular column such as price was touched, and one definition may list several events or each event may get its own focused trigger.

CREATE TRIGGER tr_LogPriceChanges
ON Products.Catalog
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Price)
    BEGIN
        INSERT INTO Audit.PriceHistory (ProductID, OldPrice, NewPrice, ChangeDate)
        SELECT d.ProductID, d.Price, i.Price, GETDATE()
        FROM deleted d
        INNER JOIN inserted i ON d.ProductID = i.ProductID
        WHERE d.Price <> i.Price;
    END;
END;
CREATE TRIGGER tr_AuditEmployeeChanges
ON HR.Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Operation NVARCHAR(10);

    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
        SET @Operation = 'INSERT';
    ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @Operation = 'UPDATE';
    ELSE
        SET @Operation = 'DELETE';

    INSERT INTO Audit.EmployeeLog (EmployeeID, Operation, ChangeDate)
    SELECT COALESCE(i.EmployeeID, d.EmployeeID), @Operation, GETDATE()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;
  • Keep trigger bodies short and fast, queuing heavy work for background jobs when a large order merely needs flagging.

  • Prevent recursion and infinite loops by configuring RECURSIVE_TRIGGERS thoughtfully and avoiding self-firing updates.

  • Handle failures explicitly with THROW, knowing an unhandled trigger error aborts both trigger and original statement.

  • Document purpose and side effects clearly because triggers run invisibly to application developers.

CREATE TRIGGER tr_ValidateOrderDate
ON Sales.Orders
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE OrderDate > GETDATE())
    BEGIN
        THROW 50001, 'Order date cannot be in the future', 1;
    END;
END;
13. 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.

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

Choosing the Right Object

Each object trades flexibility for integration. Inline functions expand into the calling query and benefit from plan analysis, whereas scalar and multi-statement functions often look like opaque blocks to the optimizer, risking weak cardinality guesses. Views only permit limited writes through a single base table, and procedures cannot be composed inside SELECT.

Watch the video

Capability

Views

Stored Procedures

Functions

Triggers

Accept parameters

No

Yes

Yes

No

Modify data

Limited

Yes

No

Yes

Return result sets

Yes

Yes

Yes (TVFs)

No

Use in SELECT/JOIN

Yes

No

Yes

No

Transaction control

No

Yes

No

Yes

Automatic execution

No

No

No

Yes

Execution plan caching

No

Yes

Varies

Yes

  • Pick views when you need parameter-free simplification and controlled exposure.

    • Hide multi-table joins and filters behind one name

    • Expose only permitted columns or rows as a security boundary

    • Offer a stable shape even if base tables evolve

  • Pick stored procedures when you need transactional business operations.

    • Run multi-statement workflows touching several tables atomically

    • Take inputs and return outputs plus status codes

    • Manage transactions and structured errors

  • Pick functions when logic must live inside queries.

    • Reuse math or formatting inside expressions

    • Supply parameterized row sets for JOIN and APPLY

    • Guarantee deterministic outputs for indexing where required

  • Pick triggers when execution must be automatic.

    • React to inserts, updates or deletes without app changes

    • Enforce rules beyond check constraints

    • Write audit trails and keep related tables synchronized

Scenario

Recommended Object

Why

Simplify a 5-table join used by many reports

View

Encapsulates complexity; no parameters needed

Process an order: validate stock, insert order, update inventory

Stored procedure

Multiple modifications in a transaction

Calculate shipping cost from weight and destination

Scalar function

Reusable calculation in queries

Return orders for a customer in a date range

Table-valued function

Parameterized result set usable in JOIN

Log every change to Salary

Trigger

Automatic, transparent audit trail

Provide read-only employee data without SSN

View

Security layer hiding sensitive columns

  • Filtering huge tables with a scalar in WHERE forces per-row evaluation; prefer an inline function or rewritten predicate.

  • Adding triggers for work better handled by explicit procedures hides logic and complicates debugging.

  • Nesting views inside views creates deep, opaque stacks that resist tuning; keep definitions shallow.

  • Using EXEC plus temp tables to feed a SELECT when a function would compose directly adds needless friction.

Diagram: Auto fire on data change?, Embed inside SELECT / JOIN?, Needs parameters or single value?, Modify tables transactionally?, Trigger: automatic response, Function: scalar or table-valued, Stored Procedure: transactional operation, View: simple read simplification

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.

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

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

Extra questions

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.

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

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.

That's the end of the free part

Up next: Advanced T-SQL: CTEs and Window Functions

12 more pages and 378 questions in the full study guide.

See what is in DP-800 SQL AI Developer Study Guide & Exam Questions
Tables, Indexes, Constraints, JSON Columns and PartitioningPage 2 of 14