Tune stored procedures with exception handling in SQL Server 2005
Exception handling was widely thought to be one of the weakest aspects of T-SQL script writing. Fortunately, this has changed in SQL Server 2005, which supports structured error handling. This tip focuses first on the basics of the new TRY…CATCH constructs and then looks at some sample SQL Server 2000 and 2005 T-SQL that produces constraint violations using transactional code.
Source: Joe Toscano April 2006
Download & read the full article at:
No comments yet.
Leave a comment
Manager
Philippe Almog MSCD
SQL Server DBA Consultant
Israel
SQLPractices RSS- SET XACT_ABORT – Use Caution with Explicit Transactions in Stored Procedures July 5, 2008Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity. However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used. I’ll describe how to protect your application from problems following timeouts and othe […]Philippe Almog
- Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 June 27, 2008This paper explains how batches are cached and reused in SQL Server 2005, and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations. Thanks to Arun Marathe & Shu Scott / Source Microsoft TechNet Download ; rea […]Philippe Almog
- Defining cascading referential integrity constraints in SQL Server June 20, 2008Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted. By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update [... […]Philippe Almog
- SQL Worst Practices Part4 May 9, 2008Making Databases Case Sensitive….. Thanks to Andy Warren Download ; read the full article at:Philippe Almog
- SQL Worst Practices Part3 May 9, 2008Not Using Primary Keys and Clustered Indexes….. Thanks to Andy Warren Download ; read the full article at:Philippe Almog
- SQL Worst Practices Part2 May 9, 2008Objects Not Owned by DBO….. Thanks to Andy Warren Download ; read the full article at:Philippe Almog
- SQL Worst Practices Part1 May 9, 2008In our business we spend a lot of time talking about ‘Best Practices’ – ways of doing things that over time have proved to be the most effective (or accurate, or whatever..). There are a couple problems with Best Practices. One is that there is no ‘book’ of them, they are scattered across a hundred [...]Philippe Almog
- Predeployment I/O Best Practices April 27, 2008SQL Server Best Practices — Published: June 5, 2007 Writer: Mike Ruthruff The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to [...]Philippe Almog
- Analysis Services Distinct Count Optimization April 26, 2008Analysis Services Distinct Count Optimization SQL Server Best Practices Article Thanks to Denny Lee Published: April, 2008 Applies To: SQL Server 2005 SP2 Download & read the full article at:Philippe Almog
- 2 Stored Procedures to Tune Your Indexes April 25, 2008Source: SQL Server Magazine APRIL 2008 Thanks to Bill McEvoy Download & read the full article at:Philippe Almog
- SET XACT_ABORT – Use Caution with Explicit Transactions in Stored Procedures July 5, 2008
-
Archives
- July 2008 (1)
- June 2008 (2)
- May 2008 (4)
- April 2008 (3)
- January 2008 (29)
-
Categories
-
RSS
Entries RSS
Comments RSS
