Lesson 1

Welcome to Queries, Cursors and Views, the second course in our SQL Server Database Implementation series. In Part 1, you learned how to design and create a relational database. In this course (Part 2), you will focus on how applications actually talk to that database: by sending queries, navigating result sets, and exposing data through reusable views.

The examples in this course target SQL Server 2019 and 2022, but the concepts apply directly to Azure SQL Database and SQL Managed Instance. Along the way, you will see how the SQL Server engine has evolved from the SQL Server 2005 era to the modern world of Intelligent Query Processing (IQP), where the optimizer can adapt to your workload in real time.

At a high level:

• Queries are how you ask questions and change data.
• Views are saved queries that act like virtual tables.
• Cursors are a procedural way to walk through query results one row at a time.

In SQL Server 2022+, most performance and scalability features assume that you are using set-based queries and well-designed views. Cursors still exist, but you will learn to treat them as a last resort when set-based logic cannot express the business requirement cleanly.

✅ Queries: your primary interface to the engine

• Role: Core mechanism for retrieving and modifying data with Transact-SQL (T-SQL).
• Usage: Ad-hoc statements, stored procedures, views, and functions.
• Features: Joins, subqueries, aggregations, window functions, CTEs, and table expressions.

SELECT CustomerName, OrderDate
FROM Orders
WHERE OrderDate >= '2025-01-01';

✅ Cursors: row-by-row processing (use sparingly)

• Role: Allow procedural, row-at-a-time logic over a result set.
• When to use: Only when a set-based operation cannot express the requirement.
• Types: STATIC, DYNAMIC, FORWARD_ONLY, KEYSET; local/global; read-only/updatable.

DECLARE order_cursor CURSOR FOR
    SELECT OrderID FROM Orders;

OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @OrderID;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- process @OrderID here
    FETCH NEXT FROM order_cursor INTO @OrderID;
END;

CLOSE order_cursor;
DEALLOCATE order_cursor;

Cursors are easy to understand, but they bypass many of the optimizations that make SQL Server fast. In this course you will often see two solutions to the same problem: a cursor-based version and a set-based version. You will learn to prefer the set-based option whenever possible.

✅ Views: reusable query building blocks

• Role: Virtual tables that wrap complex SELECT statements behind a simple name.
• Usage: To simplify joins, enforce security, and standardize business logic.
• Capabilities: Can be updatable (under certain rules), schema bound, or indexed.

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE IsActive = 1;

In later lessons, you will build views that encapsulate business rules and then compare their behavior with equivalent ad-hoc queries, including how the optimizer chooses plans for each.

Between SQL Server 2005 and the current releases, the biggest change is how the engine responds to your workload over time. Modern versions (SQL Server 2019, 2022, and the SQL Server 2025 family) include Intelligent Query Processing (IQP), a set of features that make queries more “self-healing.”

Examples you will encounter in this course include:

• Parameter Sensitive Plan (PSP) Optimization – Instead of picking one bad plan for all parameter values, the engine can store multiple plans for the same query and route each execution to the best plan for its parameters.
• Adaptive joins – SQL Server can choose between a Nested Loops or Hash join at runtime, based on how many rows actually flow through the pipeline.
• Memory grant and DOP feedback – The engine remembers when it requested too much or too little memory, or used too many CPU cores, and adjusts future executions automatically.

IQP focuses on queries and views. Cursors benefit only indirectly (for example, when the cursor’s inner query uses a multi-statement table-valued function that can be optimized with interleaved execution). This is one reason Module 2 compares batch queries vs single queries: you will see how sending work to the server in well-designed batches allows the optimizer and IQP features to do their best work, while cursor-based, row-at-a-time approaches often limit those benefits.

After completing this course, you will be able to:

1. Write INSERT, DELETE, UPDATE, and SELECT statements that retrieve and modify data safely and efficiently.
2. Use joins, subqueries, and set-based patterns to replace many procedural cursor workloads.
3. Summarize and group data using aggregates, window functions, and common table expressions.
4. Create views that simplify access, enforce security boundaries, and encapsulate common business logic.
5. Understand when a cursor is justified and how to measure its impact on performance.
6. Interpret execution plans and relate them to features such as IQP, adaptive joins, and parameter sensitivity.

You will interact with SQL Server in two ways:

1. By issuing Transact-SQL commands in tools such as SQL Server Management Studio (SSMS) or Azure Data Studio.
2. By using graphical interfaces to visualize plans, examine statistics, and explore properties of queries, cursors, and views.

Lessons combine code samples, diagrams, and guided exercises so you can see how a design decision at the T-SQL level shows up as a performance characteristic inside the engine.

Older exams like 70-029 and 70-761 focused on T-SQL syntax and database design for specific SQL Server versions. Today, Microsoft uses role-based certifications instead. If you are studying this course as part of a career plan, you will usually be choosing between:

DP-300 – Administering Microsoft Azure SQL Solutions

Choose DP-300 if your primary goal is: “I want to manage and secure Azure SQL instances.” It emphasizes:

• Deploying and configuring Azure SQL Database and SQL Managed Instance
• Securing data, accounts, and network access
• Monitoring, performance tuning, and troubleshooting query workloads
• High availability and disaster recovery

DP-300 expects you to understand how queries, views, and (occasionally) cursors affect performance. It is less about writing new T-SQL from scratch and more about diagnosing why a given workload runs slowly and how to fix it.

The “modern” alternative: DP-600 – Fabric Analytics Engineer

If you build analytics solutions on Microsoft Fabric and spend most of your time in data warehouses, star schemas, and semantic models, then DP-600 may be the better fit. It focuses on:

• Designing and implementing analytics solutions in Microsoft Fabric
• Using SQL endpoints, data model views, and transformations for reporting and BI
• Integrating with tools like Power BI and other analytics workloads

From the perspective of this course:

If your goal is…	Then focus on…
Manage, secure, and tune Azure SQL databases.	DP-300 – Azure database administration and engine behavior.
Design analytics models and SQL-based transformations in Fabric.	DP-600 – Fabric Analytics Engineer.
Deepen T-SQL query skills beyond what exams cover.	This course + advanced T-SQL and performance resources (no single exam).

As you work through the modules on queries, cursors, and views, keep your long-term direction in mind: DP-300 if you want to become a database-centric engineer, DP-600 if you see yourself building analytics and semantic models on top of SQL.

In this introductory module, you will build a mental model of how queries, cursors, and views interact inside the SQL Server engine. In Module 2, you will compare and contrast batch queries vs single queries in SQL Server 2019 and later, and see how those choices impact locking, concurrency, and Intelligent Query Processing.

In the next lesson, we will review the prerequisites for this course and ensure that your SQL Server or Azure SQL environment is ready for hands-on practice.