Implement Error Handling

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: implement try/catch/throw; use set based rather than row based logic; transaction management

What I see:

·         try/catch/throw

TRY/CATCH/THROW

                There are times that as a database developer you may want to catch errors and handle them accordingly.  This could include just letting them silently fail, or logging the parameters of the error, or re-throwing an error.  SQL Server allows us to do this very operation with the TRY…CATCH block.  If the code inside the TRY block throws an error, the CATCH block will be executed to handle the aforementioned error.  Below is an example:

begin try

       select 1/0;

end try

begin catch

       select

              error_message() as error_message,

              error_number() as error_number,

              error_severity() as error_severity,

              error_state() as error_state,

              error_line() as error_line

end catch

I use a blatant error (dividing by zero) to shift execution to the CATCH block, which simply selects the specific error parameters for viewing.  The built-in system functions of the CATCH block make available the specific (and appropriately named) portions of the error.  They are extremely useful if you do want to get the finer view of what happened to cause the CATCH block to execute.

The THROW statement is the successor to the RAISERROR() function.  It allows us to do just that:  THROW errors.  The syntax is as follows:

throw 50001, 'My Example Error Message', 1;

If the THROW statement is within a CATCH block, then parameters don’t need to be supplied:

begin try

       select 1/0;

end try

begin catch

       throw;

end catch

This allows us to re-THROW the error that caused the CATCH block to execute.

References

·         BOL reference on TRY…CATCH

·         BOL reference on THROW

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Evaluate the Use of Row-Based Operations vs. Set-Based Operations

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: when to use cursors; impact of scalar UDFs; combine multiple DML operations

What I see:

·         when to use cursors

·         impact of scalar UDFs

When to Use Cursors

                Cursors are a funny thing in SQL Server.  Many times, data professionals come from software development backgrounds.  And as programmers, we like to think row-by-row with data.  We are extremely comfortable with for loops, while loops, and other cursory language features.  And then we step into the world of the RDBMS.  We try to transfer our programming knowledge directly to database development and administration.  So often times, in the infancy of our data careers we opt to go with cursors because they are familiar ground.  While this is an understandable route, it is often the wrong one.  We need to think of data as a set-based entity, as opposed to a collection of rows.  Cursors treat the data just like that…row-by-row.  But the optimizer and SQL Server in general are much more streamlined to deal with sets instead of looping through individual rows.  As a general rule of thumb, I tend to only use cursors when set-based operations and DML statements are absolutely impossible, or when the set-based workaround is so cumbersome and unmaintainable that it because of SQL nightmare.  There is no hard and fast rule, as there is definitely going to be a time when you run into a situation when a cursor is appropriate, but it definitely should not be a daily occurance.

Impact of Scalar UDFs

                The performance impact of scalar UDFs is the performance implication that comes along with the optimizer calling the UDF each time for ever row returned.  This could lead to a notoriously bad performance problem that often comes with scalar UDFs.  For further information, read this informative post on SQL Blog by Alexander Kuznetsov.

References

·         Blog post on why Scalar UDFs can hurt performance, by Alexander Kuznetsov

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Manage Transactions

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: mark a transaction; understand begin tran, commit, and rollback; implicit vs. explicit transactions; isolation levels; scope and type of locks; trancount

What I see:

·         mark a transaction

·         begin tran, commit tran, rollback tran

·         implicit vs. explicit transactions

·         isolation levels

·         @@trancount

Mark a Transaction

                SQL Server allows us to mark transactions in order to leverage specific point recovery to a particular transaction.  For instance, with the AdventureWorks database say you mark a transaction when you modify particular data:

use AdventureWorks2012;

go

begin tran ProductionUpdate with mark

       update HumanResources.Department

       set name = 'Production Modified'

       where DepartmentID = 7;

commit tran ProductionUpdate

And then you further modify this same data:

update HumanResources.Department

set name = 'Production after Mark'

where DepartmentID = 7;

Once the log is subsequently backed up, you now have the option to restore to the committed transaction named “ProductionUpdate”.  You can accomplish this by doing the following (provided you have the correct full recovery model backups available):

restore log AdventureWorks2012

from disk = 'C:\YourBackupDir\AW_postMT.trn'

with

       recovery,

       stopatmark = 'ProductionUpdate';

go

Now by running the following query, you can see that we have restored the database to the committed portion of the marked transaction:

use AdventureWorks2012;

go

select *

from HumanResources.Department

where DepartmentID = 7;

BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN

                These three T-SQL statements are used with explicit transactions.  BEGIN TRAN tells SQL Server that an explicit transaction is starting.  It can be a named transaction, and marked (as explained above).  Subsequently, COMMIT TRAN signifies the end of a transaction by doing just that; committing it.  ROLLBACK TRAN will undo the data modification that happened during the transaction.  These explicit transaction statements are used in order to adhere to the ACID principle, particularly atomicity.  You can ensure that transaction integrity leads to data integrity.  Take the following example:

begin tran

       update HumanResources.Department

       set Name = 'Production'

       where DepartmentID = 7;

       if is_rolemember('db_owner', user_name()) = 1

              commit tran

       else

              rollback tran

The above is a relatively useless example, but it shows through the use of explicit transactions how BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN function.  It does an UPDATE of data, and if the current database user isn’t in the db_owner role, it rolls back the modified data.  Otherwise it commits the UPDATE.

Implicit vs. Explicit Transactions

                We have already talked briefly about using explicit transactions (see above), but conversely SQL Server allows us to utilize implicit transactions.  When you are operating with IMPLICIT_TRANSACTIONS ON for a particular connection, there are a handful of statements that automatically start a transaction, and that transaction will be open until either committed or rolled back.  To show an example of implicit transactions, see below:

use AdventureWorks2012;

go

set implicit_transactions on;

go

update HumanResources.Department

set Name = 'Eng'

where DepartmentID = 1;

-- now disconnect this connection

--     (i.e. close the query window)

-- open a new query window and execute the below code.

-- you will notice that the initial transaction was

-- never committed.  This is because with IMPLICIT_TRANSACTIONS ON

-- you need to commit the transaction in order for that to reflect

use AdventureWorks2012;

go

select *

from HumanResources.Department;

Isolation Levels

                SQL Server transaction isolation levels are a relatively in depth portion of locking and transactions.  You should have a thorough understanding of all the pessimistic and optimistic isolation levels.  Please see BOL for reference.

@@TRANCOUNT

                The system function @@TRANCOUNT returns the current open transactions.  It will be incremented by one for BEGIN TRAN, decremented by one for COMMIT TRAN, and appropriately set to zero for ROLLBACK TRAN.  See below for an example in order to view the return of @@TRANCOUNT with different variations of explicit transactions:

begin tran

       select @@trancount

       begin tran

              select @@trancount

              begin tran

                     select @@trancount

              commit tran

              select @@trancount

       commit tran

       select @@trancount

       begin tran

              select @@trancount

       commit tran

       select @@trancount

commit tran

select @@trancount

References

·         BOL reference on Marked Transactions

·         BOL reference on BEGIN TRANSACTION

·         BOL reference on COMMIT TRANSACTION

·         BOL reference on ROLLBACK TRANSACTION

·         BOL reference on SET IMPLICIT_TRANSACTIONS

·         BOL reference on SET TRANSACTION ISOLATION LEVEL

·         BOL reference on @@TRANCOUNT

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.