Lesson 1

In Module 1, you were introduced to queries, cursors, and views at a conceptual level: what they are, why they exist, and how they fit into the SQL Server ecosystem. Module 2 now moves from that high-level orientation into the practical details of how queries actually run inside SQL Server.

This module focuses on SQL query basics with SQL Server 2019 and 2022. You will learn how individual statements are parsed, compiled, and executed, how SQL Server treats single queries versus batches of queries, and how those choices affect performance, error handling, and transaction control. We will also introduce concepts such as system catalogs, logic inside queries, and dynamic SQL, which you will build on in later modules.

Some examples will feel familiar if you have used SQL Server before, but the goal of this module is to give you a clean, modern foundation that supports the more advanced topics to come, including cursor behavior, view design, and business rule implementation.

Every interaction with SQL Server is ultimately a batch of T-SQL statements, even if that batch contains only a single statement. However, from an application design perspective, it is helpful to distinguish between:

1. Single queries
   A single query is one T-SQL statement sent to the server and executed on its own.
   • Characteristics: One round trip from client to server; one statement to parse, compile, and execute.
   • When it works well: Simple lookups, small data changes, interactive tools (like SSMS) where you test a statement in isolation.
   • Drawbacks: When an application sends many small statements one by one, network latency and repeated compilation overhead can add up quickly.
2. Batch queries
   A batch is multiple T-SQL statements submitted together. The batch is parsed and compiled as a unit, and SQL Server executes each statement in sequence.
   • Characteristics: Fewer round trips, shared context (variables, temporary tables), and the ability to wrap the work in a single transaction.
   • Benefits: Better performance for larger workloads, clearer transactional boundaries, and fewer “chatty” calls from the application.
   • Trade-offs: Batches can be harder to debug, and long-running batches can hold locks for a longer time, affecting concurrency.

In this module you will see scenarios where sending a single statement per round trip is acceptable, and others where combining statements into a batch is essential for both performance and correctness. You will also see how the engine’s modern features—such as Intelligent Query Processing (IQP)—interact with batches and single statements.

A key takeaway as you move into later lessons: whenever possible, design your solutions so that set-based, batched operations do the heavy lifting, and avoid row-by-row processing from the client unless there is a clear reason to do so.

While detailed view design is covered later in the course, it is important at this stage to understand how views relate to query basics.

In SQL Server, a view is a named query stored as a database object. Views:

1. Behave like virtual tables – they return rows and columns when queried with SELECT, but generally do not store data themselves.
2. Provide security and abstraction – you can grant users access to a view without giving direct access to underlying tables.
3. Simplify complex logic – they encapsulate joins, filters, and calculations behind a simple name.
4. Can be indexed – in the form of indexed views, which physically store the result and can dramatically improve performance for certain workloads.

In Module 2, you will mainly consume views as query targets (for example, SELECT ... FROM SalesSummaryByMonth) while focusing on how SQL Server executes those queries. Later modules will teach you how to design and maintain views as part of a larger logical model.

After completing this module, you will be able to:

1. Describe how SQL Server executes your queries – from parsing and compilation through to execution and result return.
2. Compare and contrast batch queries with single queries – and choose the right approach for different workloads.
3. Use system catalogs in your queries – to inspect metadata about tables, indexes, views, and other objects.
4. Embed logic inside your queries – using predicates, expressions, CASE statements, and basic control-of-flow where appropriate.
5. Construct dynamic SQL safely – understanding when to use sp_executesql, how to parameterize dynamic statements, and how to avoid SQL injection.

As you work through query examples, keep these view characteristics in mind:

1. Virtual tables – A standard view does not store data physically; it exposes the result of a SELECT statement.
2. Security boundary – Views can hide sensitive columns or joins, allowing you to expose only what users need.
3. Simplification – Complex joins and calculations can be wrapped in a view so that consuming code can query a simple, stable interface.
4. Data abstraction – Because a view is a named query, you can change underlying tables (cautiously) while keeping the view interface stable for applications.
5. Update support – Some views are updatable; changes against the view propagate to the base table(s) if certain rules are met.
6. Computed columns – Views can include derived columns that compute values from base data without changing the base table schema.
7. Indexed views – By adding a unique clustered index to a view that meets specific requirements, you can materialize the result set on disk and improve performance.

You can think of a view as a reusable building block: a way to define a common query once and then reference it consistently throughout your code base.

Some topics in this module build on ideas you may only have seen briefly so far, such as:

1. User-defined functions
2. Variables and basic scripting patterns
3. Control-of-flow logic in T-SQL (for example, IF, WHILE, and CASE)

You do not need to master all of those topics before starting this module, but you should be comfortable reading simple T-SQL statements and understanding what a query does at a basic level.

One of the most important shifts in thinking is moving from a procedural, row-by-row mindset to a set-based mindset. Many developers come from languages where you write loops and process one record at a time. SQL Server can support that style using cursors and loops, but it is not where the engine excels.

In this module, I will challenge you to:

• First ask: “Can I express this requirement as a single set-based query or a small batch?”
• Only fall back to row-by-row processing if the set-based approach is impossible or unreasonably complex.

By the end of Module 2, you will have a stronger understanding of how queries behave, how batches differ from single statements, and how views participate in query execution. This foundation will prepare you for the later modules on cursors, advanced view design, and more sophisticated enterprise business rules.

In the next lesson, you will learn the practical steps for executing your queries and inspecting the results.