Composite Transact-SQL Constructs: Batches, Scripts, and Transactions - DDL Batches
(Page 4 of 4 )
Data Definition Language (DDL) is that part of Transact-SQL dedicated to the creation and modification of database objects. Some DDL statements must stand alone in the batch, including the following statements:
Create Procedure Create Trigger Create Default |
Create Rule Create View Create Function |
Set Showplan_Text Set Showplan_All Set Showplan_XML |
If any of these statements is combined with other statements in a batch, the batch will fail. Create statements must stand alone because every other statement that follows them will be interpreted as a part of the Create statement. Set Showplan_Text, Set Showplan_All, and Set Showplan_XML must stand alone in the batch because they are setting how SQL Server 2005 processes following batches and shows execution plans.
Self-sufficient Content
During compilation, the batch is converted into a single execution plan. For this reason, the batch must be self-sufficient. In the real world, this concept has vast implications for the scope of database objects, variables, and comments.
Scope of Objects
Some DDL statements can be inside batches together with other commands, but keep in mind that the resulting object will not be accessible until the batch is completed. For example, it is not possible to add new columns to the table and to access those new columns in the same batch. Therefore, the following batch will fail:
Alter Table dbo.Part ADD Cost money NULL select PartId, Cost from dbo.Part
Go
The Select statement is not able to access the Cost column, and the whole batch will fail:
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'Cost'.
Therefore, the batch has to be divided in two:
Alter Table Part ADD Cost money NULL
Go
Select PartId, Cost from Part
Go
NOTE
Some DDL statements can be combined with DML statements that reference them. For example, it is possible to create a table and insert records into it in the same batch. This is very important when you are working with temporary tables.
Scope of Variables
All variables referenced in a batch must also be declared in that batch. The following code will result in the failure of the second batch:
Declare @Name as varchar (50)
Go
Select @Name = 'Dejan'
Go
Please check back next week for the continuation of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
