Friday, January 9, 2009

04 - Transact SQL - Batches




-          Transact-SQL, an extension to the SQL database programming language, is a powerful language offering many features.

-          Transact-SQL provides the SQL Server developer with several useful functions, conditional processing methods, advanced transaction control, exception and error handling, scrollable cursors, and much more.


Here we'll cover below –


-          EXEC

-          Bind a Connection to Other Transactions using sp_bindsession and sp_getbindtoken

-          COM Automation sp_oa Procedures - Not Covered

-          Remove All Clean Buffers from the Buffer Pool

-          WAITFOR





-          A batch is a set of Transact-SQL statements submitted and executed together.

-          Batches are optimized and compiled into a single execution plan.

-          The GO keyword signals the end of a batch

-          All statements in a trigger, stored procedure, EXECUTE statement call, and sp_executesql stored procedure call are also defined as their own batches.

-          Batches are used when a specific action has to happen either before or separately from everything else in your script.


-          Batches are not to be confused with transactions; although batches are submitted and executed together, an error within the batch will not begin a rollback of statements leading up to the. Only if a transaction is used along with a batch will you see such rollback behavior.

-          Compilation errors, keeps the entire batch from compiling, will prevent all statements within the batch.

-          Runtime errors similarly, will cause the statement raising the error to fail, and possibly those statements afterwards within the batch.




-          EXEC is used to execute functions, stored procedures, extended stored procedures, and character strings within a Transact-SQL batch.

-          With EXEC, you can use strings as Transact-SQL batches by embedding them in parentheses after the EXEC statement.

-          Strings must be less than 8,000 bytes; however multiple 8,000 byte strings can be concatenated together into one EXEC statement.

-          EXEC is often used to create dynamic Transact-SQL statements, generated at run time and summarized in a string to be executed.



EXEC (<string or concatenated strings>)




DECLARE @TableName

SELECT @TableName = 'Books'

INSERT @TableName (vchBookName, chISBN)

VALUES ('Data Modeling for Everyone', '1904347002')


Produces below error –


Server: Msg 137, Level 15, State 2, Line 4

Must declare the variable '@TableName'.




DECLARE @TableName sysname

SELECT @TableName = 'Books'

EXEC('INSERT ' + @TableName + '(vchBookName, chISBN)

VALUES (''Data Modeling for Everyone'',''1904347002'')')




Bind a Connection to Other Transactions-


-          This is achieved using sp_bindsession and sp_getbindtoken.

-          This is one of the few methods of inter-connection communication between separate connections.


-          These SP's enables to bind a connection to other open transactions, so long as the transactions exist on the same SQL Server instance.

-          Once a connection is bound, two or more connections can join and participate in the same transaction until the transaction is either rolled back or committed.




sp_getbindtoken <@variable_to_grab_token> = <return_value> OUTPUT,



sp_bindsession <bind_token or NULL>



The first session begins a transaction, creates a table, and returns a bind token.

BEGIN TRAN FunTransaction

CREATE TABLE FunValue (vchFunCode varchar (10) NOT NULL)

DECLARE @Token varchar (255)

EXEC sp_getbindtoken @Token OUTPUT

PRINT @Token



Output - 'f@1I2@j0iQ?0LcXjR5XV3=5---.aC---


Now a second session binds itself to the open transaction, enters some data into the new table, and commits the transaction.



Note that the value passed to sp_bindsession is the same as @Token.

EXEC sp_bindsession 'f@1I2@j0iQ?0LcXjR5XV3=5---.aC---'




COMMIT TRAN FunTransaction





Remove All Clean Buffers from the Buffer Pool


-          If you wish to test the performance of queries with a cleared buffer cache (no cached query plans), runn DBCC DROPCLEANBUFFERS.

-          This remove all clean buffers from the buffer pool. This has the same impact on query performance as restarting your SQL Server instance.








-          The WAITFOR function specifies a length of time or time to wait, suspending activity prior to executing code following the WAITFOR statement.

-          WAITFOR is useful for making sure a certain period of time passes before running a chunk of code.




WAITFOR <DELAY 'time'> or TIME <'time_to_wait_until'>



WAITFOR DELAY '00:00:10'

PRINT 'I waited 10 seconds!'




PRINT 'Well I waited until 7:14 PM!'






Thanks & Regards,

Arun Manglick || Tech Lead

No comments:

Post a Comment