Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
2  
+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
    
"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 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

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.

share|improve this answer

Your Answer

 
discard

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

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