开发者

Batch vs SQL statement

a)

A SQL statement is a single SQL command (for example, SELECT * FROM table1 or SET NOCOUNT ON). A batch on the other hand, is a number of SQL statements sent to the server for execution as 开发者_StackOverflow社区a whole unit. The statements in the batch are compiled into a single execution plan. Batches are separated by the GO command

So the only difference between SQL statement and a Batch is that each SQL statement is sent to server as a separate unit and thus is compiled separately from other SQL statements, while SQL statements in a Batch are compiled together?

b) I assume one of major differences between a stored procedure and a Batch is that stored procedures are precompiled while Batches aren’t?

thanx


a. Only if each SQL statement is run individually (say in SSMS or on the client).

Two statements = "a batch" always even if no GO is involved. GO merely tells a tools like SSMS to break up the submits to the engine.

b. not quite true. A stored proc is pre-parsed but not compiled into an execution plan until invoked and not in the plan cache already. A batch is parsed and compiled in one go and may generate a reusable plan.

Edit, after comment:

  • The terms "statement" and "batch" are 2 different concepts
  • Any text sent to the DB engine is a batch
  • text is literally that: no processing is done by the client tools: only text is sent to the DB engine
  • the text consists of SQL statements

So

  • A batch consists of at least one character of text/one statement (but could be 2, 20 or 20,000 statements)
  • GO tells the SQL tools where to break up a "block of text"/"collection of statements" into separate DB engine calls (= batches)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜