390
Part IV: Programming with T-SQL
Transact-SQL Fundamentals
T-SQL is designed to add structure to the handling of sets of data. Because of this, it does
not provide several language features that application development needs. If you do a lot
of application programming development, you’ll fi nd that T-SQL is in many ways the exact
opposite of how you think when programming in VB, C#, Java, or any other structured
development language.Best Practice
SQL Server 2005 added the capability to create stored procedures, functions, and triggers using .NET
and the common language runtime (CLR). Nevertheless, T-SQL is the native language of SQL Server
and remains the best choice for any data-oriented task.T-SQL Batches
A query is a single SQL DML statement, and a batch is a collection of one or more T-SQL
statements. The entire collection is sent to SQL Server from the front-end application as a
single unit of code.SQL Server parses the entire batch as a unit. Any syntax error will cause the entire batch
to fail, meaning that none of the batch executes. However, the parsing does not check any
object names or schemas because a schema may change by the time the statement executes.Terminating a Batch
A SQL script fi le or a Query Analyzer window may contain multiple batches. If this is the
case, a batch-separator keyword terminates each batch. By default, the batch-separator
keyword is GO (similar to how the Start button is used to shut down Windows). The
batch-separator keyword must be the only keyword in the line. You can add a comment
after the GO.The batch separator is actually a function of SQL Server Management Studio and
SQLCMD, not SQL Server. It can be modifi ed in the Query Execution page by selecting
Tools ➪ Options, but it isn’t recommended that you create a custom batch separator (at least
not for your friends).Because the GO batch terminator tells Management Studio’s Query Editor to send the batch
to the connected SQL Server, it can be used to submit the batch multiple times. The follow-
ing script demonstrates this poor man’s cursor:PRINT 'I'm a batch.';
go 5 -- will execute 5 timesc16.indd 390c16.indd 390 7/30/2012 5:38:04 PM7/30/2012 5:38:04 PM
http://www.it-ebooks.info