35

What is the difference between SQL batch, T-SQL statement and Remote Procedure Call?
How can I tell if part of the T-SQL code is a batch or statement?

24

Well, I suppose you're talking mostly about the Profiler classes, but the explanation stands anyway.

An SQL batch is a set of one or more statements grouped together and separated by a GO statement. EG: more SELECT and INSERT statements form a batch if they have a GO at the end.

A RPC call is a call that comes from a client application to the database. EG: a windows service, a web application, a windows app, whatever needs a connection to the database actually makes a RPC call.

Now, in Profiler you'll see everything that touches the database server. A batch from Management Studio, an RPC call (which is either a batch or a stored procedure call) from an external application, a procedure execution from Management Studio.

Each of them is formed of TSQL statements, so this Profiler class is useful in case you want to expand the execution further, to see what's actually executed. What inserts, selects..etc.

The easiest way to look at them in Profiler is to enable only End RPC call, or End batch call and you'll see there all the statistics needed (duration, IO, CPU). Then, move further by enabling TSQL Statements class and dig deeper.

  • 4
    +1 Just to specify, GO is the accepted and default batch terminator of the popular clients we use (i.e. SSMS and sqlcmd), but it is worth noting that the actual GO string as a batch terminator is liable to change and is configurable. – Thomas Stringer Feb 10 '13 at 19:23
  • 1
    "An RPC call (which is either a batch or a stored procedure call)". So everything is RPC in the end? Can you clarify that? – Iain Samuel McLean Elder Feb 15 '13 at 17:02
  • No, I wanted to say that an RPC call is formed of either a stored procedure call or a batch of more statements. What you actually see in this Profiler event is either a single procedure or a batch of more statements. – Marian Feb 15 '13 at 17:30
  • Please see some explanation here. Some other info here. – Marian Feb 15 '13 at 17:35
14

Batch vs T-SQL Statement

This is clearly defined in SQL Server BOL here

A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. Go is a batch separator used in most client application including SSMS.

SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.

In a simple term based on my understanding RPC is when you execute a stored proc using client API (e.g. in ADO.net CommandObject. Execute method)

A more detail explanation is found in one of the internet newsgroup posting here:

RPC" vs "batch" is the TDS execution mode that ADO.NET (or any SQL Server client) uses. When a plain SQL statement with no parameters is executed, we use a "batch". When a stored-proc is executed, we use RPC (this is not the same as RPC as the stand-alone network remote procedure call, we just happen to call this mode RPC in TDS (the SQL Server network protocol)). Also, if you execute a batch with parameter, we actually use a stored-proc called sp_executesql, and pass the SQL statement itself and the rest of the parameters to it, so it also shows up as RPC.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

  • so if one statement in batch fails, entire batch is rollbacked automatically? – MonsterMMORPG Jun 28 '16 at 11:43
  • No, each statement in the batch is committed autonomously, unless the batch is inside an active transaction or you set IMPLICIT_TRANSACTIONS ON. Batches and transactions are two different concepts. I know that the question is very old, I'm just leaving this here for future readers. – spaghettidba Mar 12 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.