- 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 –
- 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
- 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>)
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
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---'
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.
- 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!'
WAITFOR TIME '19:14'
PRINT 'Well I waited until 7:14 PM!'
Thanks & Regards,
Arun Manglick || Tech Lead