SQL-Query Basics  «Prev  Next»
Lesson 3 Batch queries
Objective Describe batch queries, and compare them to single queries.

SQL Batch Queries in SQL Server 2022

SQL Batch Queries refer to groups of one or more Transact-SQL (T-SQL) statements that are sent to and executed by SQL Server as a single unit. In SQL Server 2022, batch processing remains a fundamental concept for query execution and optimization.
Key Characteristics of SQL Batch Queries
  1. Definition:
    • A batch is a set of SQL statements submitted together and parsed/executed as a unit
    • Batches are separated by the GO statement in tools like SSMS (though GO is not a T-SQL command)
  2. Execution Context:
    • Each batch runs in a single execution context
    • Variables declared in one batch aren't visible to subsequent batches
    • Errors may affect batch execution differently depending on severity
SQL Server 2022 Enhancements for Batch Processing
  1. Intelligent Query Processing (IQP) Improvements:
    • Enhanced batch mode memory grant feedback
    • Improved batch mode adaptive joins
    • Degree of Parallelism (DOP) feedback for batch mode operations
  2. Parameter Sensitive Plan (PSP) Optimization:
    • Better handling of batches with parameterized queries that have varying cardinality
  3. Memory-Optimized TempDB Metadata:
    • Improved performance for batches that heavily use temporary tables

Batch Processing Examples
-- Batch 1: Creates a table and inserts data
CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100)
);
INSERT INTO dbo.Customers VALUES (1, 'John Doe');
GO  -- Signals end of batch

-- Batch 2: Queries the table (can't see variables from Batch 1)
SELECT * FROM dbo.Customers;
GO

Best Practices for Batch Queries in SQL Server 2022
  1. Transaction Management:
    • Group related operations in the same batch when they should succeed or fail together
    • Be aware that batches don't automatically imply transactions
  2. Error Handling:
    • Use TRY/CATCH blocks within batches for comprehensive error handling
    • Understand that compilation errors prevent the entire batch from executing
  3. Performance Considerations:
    • Large batches can reduce network round trips but may increase memory usage
    • Consider using table-valued parameters for batch data operations
    • Leverage batch mode execution for analytical workloads
  4. Security:
    • Use parameterized batches to prevent SQL injection
    • Consider the EXECUTE AS clause for specific permission contexts
Batch vs. Transaction Important distinction:
  • Batch: A group of statements sent to the server together
  • Transaction: A logical unit of work that must be completed as a whole (using BEGIN TRANSACTION, COMMIT, ROLLBACK)

SQL Server 2022 Query Performance Tuning

Send SQL Batch Statements for execution in SSMS

To send SQL batch[1] statements all at once for execution in SQL Server Management Studio (SSMS), you can follow these steps:
βœ… What is a SQL Batch Statement?
A batch is a group of one or more T-SQL statements sent to SQL Server for execution as a unit. Batches are separated by the `GO` keyword in SSMS.
🧭 Steps to Execute SQL Batch Statements in SSMS
  1. Open SSMS and Connect to a Server
    • Launch SSMS
    • Connect to your SQL Server 2022 instance
  2. Open a New Query Window
    • Click New Query in the toolbar
    • Make sure the correct database is selected from the dropdown
  3. Enter Your SQL Batch Script
    
    USE AdventureWorks2022;
    GO
    
    CREATE TABLE dbo.TestBatch (
    Β  Β   ID INT PRIMARY KEY,
    Β  Β   Name NVARCHAR(50)
    );
    GO
    
    INSERT INTO dbo.TestBatch (ID, Name)
    VALUES (1, 'Thomas'), (2, 'Anna');
    GO
    
    SELECT * FROM dbo.TestBatch;
    GO
        
    
    • Each section of code between GO is treated as a batch
  4. Execute All Batches at Once
    • Click the Execute button or press F5
    • SSMS will send each batch to SQL Server sequentially

πŸ“ Notes
  • GO is not a T-SQL command; it’s a batch separator understood by SSMS and sqlcmd, but not SQL Server itself
  • You cannot use GO inside stored procedures or functions
  • All batches are executed in sequence, not in parallel

🧰 Bonus Tip (Automated Batch Execution)
You can also execute a `.sql` script file containing multiple batches using the `sqlcmd` utility:
sqlcmd -S localhost -d AdventureWorks2022 -i C:\path\to\batch-script.sql

❓ Question: What is a batch?

A batch is a group of one or more Transact-SQL (T-SQL) statements that are submitted together to SQL Server for execution. If there are no `GO` keywords in a set of statements, they are all considered to be in a single batch.
βœ… Advantages of Using Batches
If a syntax or compile-time error occurs in a batch, SQL Server may stop executing the remaining statements in that batch. This can be helpful during troubleshooting because it isolates the problematic code. However, runtime errors (such as divide-by-zero or constraint violations) may allow the batch to continue unless explicitly handled.
πŸ” Variables and Batches
Each batch is treated as a separate compilation unit. Therefore:
  • Any variables (DECLARE @var INT, etc.) used in a batch must be declared within that batch.
  • Variables do not carry over across batches unless passed via a stored procedure or function.

⚠️ When Not to Use Batching with Other Statements
The following `CREATE` statements must be executed as single-statement batches (i.e., no other SQL statements can appear in the same batch):
  • CREATE PROCEDURE
  • CREATE TRIGGER
  • CREATE VIEW
  • CREATE DEFAULT
  • CREATE RULE

This is because SQL Server requires these definitions to be compiled in isolation from other statements.
πŸ”§ How to Create a Batch: Using the `GO` Statement
The `GO` statement is not a T-SQL command, but rather a batch separator understood by SSMS, `sqlcmd`, and other tools. All statements between two `GO` commands form one batch. If no `GO` keyword is used, all statements in the query editor are submitted as one batch. Think of there being an implicit `GO` at the end of your submission.
πŸ§ͺ Examples
Single Batch (No GO) : The following is treated as one batch:
UPDATE Employees
SET FirstName = 'Anthony',
    LastName = 'Mann'
WHERE EmployeeID = 101;

SELECT *
FROM Employees
WHERE EmployeeID = 101;

Multiple Batches (Using GO):
USE Timesheets;
GO

UPDATE Employees
SET FirstName = 'Anthony',
    LastName = 'Mann'
WHERE EmployeeID = 101;

SELECT *
FROM Employees
WHERE EmployeeID = 101;

In this example:
  • The first USE statement is one batch.
  • The UPDATE and SELECT are part of the second batch.

[1]Batch: A set of Transact-SQL commands that are sent to the SQL Server engine, all at one time. Batches are separated by GO keywords.

SEMrush Software 3 SEMrush Banner 3