A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.
Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks.
SQL Server has an exception model to handle exceptions and errors that occurs in T-SQL statements. Exception handling in Sql Server is like as exception handling in other programming language. To understand exception handling, first we need to know how many types of exception we have in Sql Server.
Sql Server provides xquery methods to query xml file or xml data. Using these methods we can Insert, Update, Delete data in xml file or in XML Data Type variable. In Sql Server XQuery statements are case sensitive since xml is case sensitive. Hence while query to xml data remember this thing.
The main advantage of stored procedure is, to execute T-SQL statements in less time than the similar set of T-SQL statements is executed individually. The reason to take less time is that the query execution plan for the stored procedures is already stored in the "sys.procedures" system defined view.
Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).
In database, you need to do Insert, Update and Delete. If we want to make a reliable and high performance system then these four operations must be implemented by stored procedures. Stored procedure also prevents Sql Injection attacks and reduce network traffic.
A stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server. Benifit of Stored Procedures is that they are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic.
A SQL Injection attack is an attack mechanisms used by hackers to steal sensitive information from database of an organization. It is the application layer means front-end attack which takes benefit of inappropriate coding of our applications that allows hacker to insert SQL commands into your code that is using sql statement.
SQL Server 2008 and 2005 have five system defined databases: master, model, tempdb, msdb, and resource. These databases are used by SQL Server for its own operation and management. Let’s see these database use and function.
In Sql Server, we have group by clause for grouping the records of the database table(s) according to our need. We use having clause to filter data that we get from group by clause.Having clause operates only on group by clause means to use having clause we need to use group by clause first. Lets go through both the clauses.
xml data type was introduced in SQL Server 2005 to work with XML data. Using this data type, we can store XML in its native format and can also query/modify the xml data within the xml. We can use xml data type like as :
Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like as table.
Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers.
Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements.