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)
精彩评论