Exception Handling In SQL Server

Similar to other programming languages SQL server also supports exception handling i.e handles the exceptions, provide a proper message to the user regarding exception and avoid abnormal termination of the program.

Up to SQL sever 2000, a system variable called @@Error is use to get information about the exception and handle the exception but from SQL Server 2005 onwards exception handling is done by using try catch.

Within SQL server,  a try block can have only one catch block.

Syntax:

         begin try

          <statements>

         end try

          begin catch

          <statements>

         end catch

Error Functions

To get information about raised exception a set of functions are provided, which are collectively called as Error Functions are new in SQL 2005.

• Error_Number() : Returns the unique number of raised error.

• Error_Message() : Returns the message associated with the current  error raised.

• Error_Severity() : Returns the severity of the current error raised.

• Error_State() : Returns the state of current error raised.

• Error_Procedure() : Returns name of the procedure in which exception occurs.

• Error_Line() : Returns the line number within the procedure where error occurs.

Geting List of SQL Server error messages:
select * from sys.messages

You can add your own error messages to the error messages list of SQL server using the stored procedure sp_add message.

Raising Errors Manually:
There are situations where system will not raise an exception for your requirement and you want to raise exception manually for this use the riase error function that has following syntax :

raiserror(msgid/msgtext,severity,state[,arguments])

Triggers

      Triggers are stored sub-programs that will be automatically executed based on specified event.

Differences between Stored Procedures, User Defined Functions and Triggers are as follows-->

• Stored Procedures and User Defined Functions can be called by the user manually but triggers cannot be called by the user manually and they will be automatically invoked.
• Stored Procedures can return a value with output parameters and User Defined Functions can return a value with return statement but triggers cannot return a value either with output parameters or return statement.
• Stored Procedures, User Defined Functions can take arguments but triggers cannot take arguments.

Based on event specified on a trigger, triggers are classified into DDL triggers and DML triggers. When the trigger is created by specifying a DDL command as event then that trigger is called as DDL triggers and when a trigger is created by specifying a DML command then that trigger is called as DML triggers. 

     

      DDL triggers are new in SQL 2005. DML triggers have the following three purposes-->

• Create procedure integrity constraints.
• Record auditing information of a table.
• Allow insert,update,delete on complex views.

Stored sub programs in SQL

• Stored subprograms are the programs that are permanently stored in the database. Execution of stored sub-programs will be fast compare to independent SQL statements.

• Hence to perform any operation again database from the front end application like .Net it is recommended to use stored sub programs than independent SQL statements to improve the performance of application.

•   Before executing an independent SQL statements , SQL server has to perform parsing execution plan generation and cost estimation. During Parsing it will verify syntax of statement, availability of object refers in the statement, permissions of current user to perform specified operations on specified object and data type compatibility. As all this steps takes time execution will be slow and it affects performance of application.
• Stored procedures are classified into :

• Stored procedures
• User defined procedures
• Triggers

Stored Procedure: Stored procedures are the stored subprograms that cannot return a value with return statement. A stored procedure can contain return statemnet without a value to return and purpose of this is to exit from stored procedure.

 Syntax:
create procedure<procname>[(<parameters>)]
[with encryption] as begin
<statements>
end

User-defined functions in SQL

User defined functions are stored subprograms that must return a value with return statements. User defined functions are classified functions.

• Scalar function: This are the user defined functions that return a single value. 

• Valued functions: This are user defined functions that returns a table.  Table valued functions are classified into inline table valued functions and multistatement table values statement.

• Inline Table valued functions:This are the table valued functions that contain a single statement i.e the return statement with a select statement within it, whose results will be converted inline as a table.

Inline table valued function must not contain the keyword begin and end.

• Multistatement valued functions: This are table value functions that contain multiple statements. In case of multistatemnet table values function you have to create a table in a function manual, insert rows into it and return it manual.

TSQL Programming

  From last two days,we have started learning TSQL Programming. TSQL is non procedural or 4th Generation language because in TSQL we need to specify only what is the problem to solve and no need to specify how to solve the problem step by step hence working with TSQL will be easy but because of non-procedural value it is not possible to solve some complex queries .

  To overcome this problem Microsoft provides programming language feature into SQL which also are collectively called as TSQL Programming.

We have done  some basic programming using :

•  Conditional Control Statements
• Looping Control Statements
• Cursor

Since we completed practicing all basic queries, now we have started with TSQL Programming which is the last part of SQL Server.

The Concept of 'Joins' in SQL Server

Combining data from multiple tables using single select statement is called as a JOIN.
Joins are classified into

• Inner Join.
• Outer Join
• Cross Join

 Inner Join are again classified into:

• Equi Join
• Natural Join
• Non-Equi Join
• Self Join

Outer Join are classified into

• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join

1. Inner Join:  Inner Join is the join that can display only the rows that satisfy the given join condition.
2. Equi Join: The Inner Join that uses equal operator in the join condition is called as Equi Join.
3. Natural Join: An inner join is called as natural join only when it satisfies the following three condition.

• Join is Equi Join.
• All common columns in the table must be in the joined condition.
• Only one set of common columns is displayed in the output.

   4. Non Equi Join: The inner join that uses the operator other than equal in the join condition is called as non-Equi Join.

    5.  Self Join: A join that joins a taable to its self is called as Self Join.

• Outer Join:  Outer Join is a join that can display the rows that does not satisfy the given join condition along with the rows that satisfy the given condition.

1. Left Outer Join: Left outer Join is an Outer Join that can display the rows that does not satisfy the given condition only from left hand side table. To perform Left outer join with ANSI syntax use the keyword Left Outer Join and to perform Left Outer Join with non-ANSI syntax use the keyword *= in the join condition.
2. Right Outer Join: Right outer Join is an Outer Join that can display the rows that does not satisfy the given condition only from right hand side table. To perform right outer join with ANSI syntax use the keyword Left Outer Join and to perform Right Outer Join with non-ANSI syntax use the keyword =* in the join condition.
3. Full Outer Join:  Full outer Join is an Outer Join that can display the rows that does not satisfy the given condition only from both the tables. To perform full outer join with ANSI syntax use the keyword full Outer Join and to perform Full Outer Join with non-ANSI syntax perform union on left outer join and right outer join statements.
4. Cross Outer Join: The join that joins every row in the first table with every row in the second table is called as Cross Join.

Something About SQL Server

      SQL Server 2012 is the latest version of a database server product that has been evolving since the late 1980's. Microsoft SQL Server originated at Sybase SQL Server in1987. In 1988, Microsoft, Sybase, and Aston-Tate ported the product to OS/2. Later Aston-Tate dropped out of the SQL Server development picture, and Microsoft and Sybase signed a co-development agreement to port SQL Server to Windows NT. The co-development effort cumulated in the release of SQL Server 4.0 for Windows NT. After the 4.0 release, Microsoft and Sybase split on the development of SQL Server, Microsoft continued forward with future targeted on Windows NT platform while Sybase moved ahead with releases targeted for the UNIX platform, which they still market today. SQL Server 6.0 was the first release of SQL Server that was developed completely by Microsoft, In 1996, Microsoft updated SQL Server with the 6.5 release. After a 2 year development cycle Microsoft released the vastly updated SQL Server 7.0 release in 1998.
           SQL Server embodied many radical changes in the underlying storage and database engine technology used in SQL Server, SQL Server 2000, the accumulation of another 2 year effort, was released in September,2000. The move from SQL Server 7.0 to SQL Server 2000 was more than an evolutionary move that dint entail the same kind of massive  changes that were made in the move from 6.5 to 7.0. Instead, SQL Server 2000 was build incrementally on the new code base that was established in 7.0 release. Starting with SQL Server 2000, Microsoft began releasing updates to the basic release of SQL Server in the following year starting with XML for SQL server web release 1, which added several XML features including the ability to receive the result set  as an SQL document.
             The next year they renamed the web release to the more succinctly title SQLXML 2.0, which among other thing added the ability to update the SQL Server database using XML updategrams. This was quickly followed by the SQLXML 3.0 web release which included the ability to expose stored procedures as web services. 2 years later, Microsoft SQL Server release history accumulates with the release of SQL Server 2005. SQL Server 2005 uses the same basic architecture that was established with SQL Server7 and it adds to this all the features introduced with SQL Server 2000.

The following timeline summarizes the developmental history of SQL Server

• 1987 Sybase releases SQL Server for UNIX.
• 1988 Microsoft, Sybase and Aston-Tate port SQL Server to OS/2
• 1989 Microsoft, Sybase and Aston-Tate release SQL Server 1.0 for OS/2
• 1990 SQL Server 1.1 is released with support for Windows 3.0 clients.                           Aston-Tate drops out of SQL Server development.
• 1991 Microsoft and IBM end join development of OS/2.
• 1992 Microsoft SQL Server 4.2 for 16 bit O/S 2 1.3 is released.
• 1992 Microsoft and Sybase port SQL Server to windows NT.
• 1993 Windows NT 3.1 is released.
• 1993 Microsoft and Sybase release version 4.2 of SQL Server for windows NT.
• 1994 Microsoft and Sybase code development of SQL Server officially ends. Microsoft continues to develop the Windows version of SQL Server. Sybase continues to develop the UNIX version of SQL Server.
• 1995 Microsoft releases version 6.0 of SQL Server.     
• 1996 Microsoft releases version 6.5 of SQL Server.    
• 1998 Microsoft releases version 7.0 of SQL Server. 
• 2000 Microsoft releases SQL Server 2000
• 2001 Microsoft releases XML for  SQL Server web release 1.  
• 2002 Microsoft releases SQLXML 2.0 ( renamed from XML for  SQL Server).
• 2002 Microsoft releases SQLXML 3.0.
• 2005  Microsoft releases SQL Server 2005 on November 7th,2005.
• 2008 Microsoft releases SQL Server 2008 on August 6th,2008.
• 2012  Microsoft releases SQL Server 2012 on March 7th,2012.