Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?


share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
So what's the cardinality between batch and transaction? Is it possible to have multiple batches per transaction? How about vice versa? –  Sleepless Mar 30 '10 at 9:19
    
As far as I am aware it is possible to have multiple transactions per batch but it is not possible to have multiple batches per transaction. –  Cromulent Mar 30 '10 at 10:32
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.