SQL Server: Statements vs. Batches vs. Transactions vs. Connections
My question is simple: What are the differences / similarities / cardinalities between
- Transactions
- Batches
- Connections and
- Statements
in SQL Server?
As far as I understand a connection is a single communications channel between a SQL Server instance and a client within which collections of statements grouped as batches are executed. A batch is either implicitly or explicitly mapped to one or several transactions. Is this correct?
Pretty much.
A batch is just that, a batch of commands that need to be executed. A transaction is a set of commands that are guaranteed to succeed or fail totally (i.e it won't complete half the commands and then fail on the rest, if one fails they all fail).
As far as I am aware SQL Server makes use of connection pooling so I wouldn't rely on the one connection per client idea.
Transactions and Batches are two independent concepts. Both can be used in a one to many configuration.
Transaction blocks are a single "unit of work", a concept that committed sql must either fully work or not work at all. For example, if you update two tables linked to each other; both must succeed for the data change to be committed. [https://msdn.microsoft.com/en-us/library/ms174377.aspx]
Batch is a Microsoft concept. With the tools created by Microsoft such as sqlcmd and osql, the batch simply ensures a single execution plan. For example, if you create a variable and use it outside the batch, the tool will throw an error. [https://msdn.microsoft.com/en-us/library/ms188037.aspx]
So, you can have multiple batches that update multiple tables inside one transaction block. As far as they do not violate individual batch execution plans that is.
Also, within a batch, you can have multiple transaction blocks, ensuring data integrity between database entities like tables.
Connection is simply the communication handshake that approves one to run queries on server.
Statements are individual lines forming a query. GO (T-Sql batch separator) and BEGIN TRANSACTION (ANSI SQL for starting new transaction block) are both statements.
Batches and transactions exist at the same level. A batch is a collection of otherwise unrelated SQL commands. A transaction is a collection of SQL commands that operate (as far as all other users of that database are concerned) as one statement.