SQL and Transact-SQL - SQL Programmers

SQL and Transact-SQL

03/02/2010

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


Transaction control:

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'  
        BEGIN  
            SELECT EmployeeName FROM Employee  
        END  
    ELSE IF Mode = 'INSERT'  
        BEGIN  
            INSERT INTO Employee (EmployeeName) VALUES ('John')  
        END 

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  
    BEGIN  
        -- increment a variable for the month  
        SELECT  @month = @month + 1  
        PRINT @Month  
    END 

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 ()  
    BEGIN  
        PRINT 'Ending'  
        GOTO End0fFunction  
    END  
      
    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:

    BEGIN  
        PRINT 'Waiting'  
        -- The WAITFOR DELAY command will make the program wait 1 hour.  
        WAITFOR DELAY '01:00:00'  
    END

Error handling:

  • 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:

SQL 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