Transact-SQL (T-SQL) is Microsoft's enhanced version of the Structured Query Language (SQL). The Transact-SQL language was introduced by Sybase. It includes transaction control, enabling conditional processing, error handling, row processing, declared variables and more. When T-SQL is written and run, it can ultimately impact your server’s performance.
The following are some of the features of T-SQL
- Transaction control
- Conditional processing with IF . . . ELSE
- Iterative processing with WHILE
- Branching control with GOTO
- Delay control with WAITFOR
- Error handling
The purpose of transaction is to ensure that a set of modifying statements are atomic, namely that either all steps succeed or all steps fail. Keep the transaction opened as short as possible. Open transactions just before you start the modifying statements in real tables and close it as soon as you are done. Use transactions only when you modify the real tables. Always make sure to commit or rollback opened transactions. Always use try/catch block to catch the errors and maybe rollback the transactions on error. Do not leave transactions outside the TRY/CATCH blocks. Before you rollback the transactions, always make sure at least one transaction is opened by checking the variable @@TRANCOUNT as illustraed below:
- BEGIN TRY
- BEGIN TRANSACTION
- -- Your modifying commands …
- COMMIT TRANSACTION
- END TRY
- BEGIN CATCH
- -- Undo the above changes so the database is consistent.
- IF @@TRANCOUNT > 0
- ROLLBACK TRANSACTION
- END CATCH
Conditional processing with the IF . . . ELSE :
The IF…ELSE structure is used to make the choices based on the condition. You can nest as many IF…ELSE structures as you want. The IF . . . ELSE construct requires the use of a BEGIN . . . END block if more than one SQL or Transact-SQL command is dependent upon the condition. Here’s an example of an IF . . . ELSE statement:
- IF Mode = 'SELECT'
- SELECT EmployeeName FROM Employee
- ELSE IF Mode = 'INSERT'
- INSERT INTO Employee (EmployeeName) VALUES ('John')
Iterative processing with WHILE:
The Transact-SQL WHILE extension provides iterative controls that allows a single block of Transact-SQL code to be repeated. You can even nest loops within other loops. This example shows what a WHILE loop looks like:
- DECLARE @month INT,
- SELECT @month=0
- WHILE @month <= 12
- -- increment a variable for the month
- SELECT @month = @month + 1
- PRINT @Month
Branching control with GOTO:
GOTO transfers control from one executable statement to another labeled section in the current Transact-SQL program. Here's a Transact-SQL program that illustrates commands:
- IF JOINDATE > GETDATE ()
- PRINT 'Ending'
- GOTO End0fFunction
- RETURN 1
Delay control with WAITFOR:
The WAITFOR command provides Transact-SQL programs with delay controls. Here's a Transact-SQL program that illustrates the commands:
- PRINT 'Waiting'
- -- The WAITFOR DELAY command will make the program wait 1 hour.
- WAITFOR DELAY '01:00:00'
- Transact-SQL allows you to detect the occurrence of errors in the course of executing a program. Errors in Transact-SQL fall into three categories: informational, warning, and fatal.
- Informational errors output a message but do not cause the program to abort.
- Warning messages output an error message and abort the currently executing SQL or Transact-SQL statement but do not abort the entire program or Transact-SQL batch.
- Fatal errors are bad. They send an error message and a notification to the operating system error log. Furthermore, fatal errors terminate the Transact-SQL program where the error occurred.
- Transact-SQL uses a linear code model for error handling. So, if you don't check for a particular level or type of error, Transact-SQL will not provide any special response to it. For that reason, it is very important for you to properly check and evaluate error conditions throughout the execution of your Transact-SQL programs.
The following Transact-SQL block checks to see if certain data exists within the database. If the data doesn't exist, then an ad hoc error message is raised:
- IF EXISTS (SELECT * FROM authors WHERE Firstname = 'Elmer' AND Lastname 'Fudd')
- PRINT "Author Found "
- ELSE RAISERROR('Warning! Author Not Found!',16,1)
BULK INSERT is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added
Difference between SQL and T-SQL:
|The SQL queries are submitted individually to the database server.||The group of SQL queries are submitted to the server in a single go.|
|It is the data oriented language for selecting and manipulating sets of data.||It follows the Procedural approach of executing the queries.|
|The front end of the application Can communicate with several relational database management systems||Database communication can communicate with only MS SQL Server|
|Standard version of queries includes DDL, DML and select||Set of SQL like stored procedure, function and define own data types|