04 - Transact SQL - Batches

Hi,

 

 

-          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

 

 

Batches

 

-          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.

 

Use EXEC

 

-          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>)
Delay	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'.
Time	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, <@optional_for_xp_flag>   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---' GO INSERT FunValue VALUES ('Fun SQL') 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.

 

DBCC DROPCLEANBUFFERS

 

 

Use WAITFOR

 

 

-          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'>
Delay	WAITFOR DELAY '00:00:10' PRINT 'I waited 10 seconds!'
Time	WAITFOR TIME '19:14' PRINT 'Well I waited until 7:14 PM!'

 

 

 

 

Thanks & Regards,

Arun Manglick || Tech Lead