I have been looking at parts of SQL Server 2005 since the June CTP and been implementing SQL 2005 in production and development environments since December last year.  The difference between investigating the features and doing an implementation is that in an actual implementation you use the most practical and useful features, rather than the cool features that you may use one day in some future architecture.

SQL Server Reporting services is cool and useful - but is a separate bundled package - so I consider it a new product, not a new feature.  Similarly with SQL Server Integration Services - a great step in the direction of visual programming and DSL's (Domain Specific Languages).  Other features are intriguing and I intend to put them to use on some future architecture; the Service Broker, Database Mirroring and CLR Integration are some of the features that pique my interest.

Currently there are two things that I used immediately and so extensively that I almost can’t imagine what it was like developing for SQL Server a few months ago – those two are Schemas and Error Handling.  I would have to say that the addition of try-catch block has made the biggest difference.

T-SQL is often viewed as a second-rate language compared to C#, Java or other modern (or at least Object Oriented) languages.  However the set-based bias of T-SQL has always meant that a lot of functionality can be developed in stored procedures – some would argue too much.  The addition of the try-catch block brings T-SQL closer to being a more elegant and usable language.  Handling errors, particularly within the scope of transactions was difficult and cumbersome prior to SQL2005 – the need to inspect @@ERROR after every statement meant that the code was either riddled with too many error checks and conditional statements to exit elegantly or had insufficient error handling that would leave data in an inconsistent state.

Although not necessarily a candidate for a ‘best practice’ award, I have a chunk of boilerplate code that I put in virtually every stored procedure – allowing me to handle errors gracefully and to roll back transactions.  This code even reports the exact line number that an error occurred (including errors that occur in triggers or nested stored procedures)

BEGIN TRANSACTION

BEGIN TRY

  -- Do stuff

END TRY

BEGIN CATCH

  IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION

  DECLARE @ErrorMessage nvarchar(4000)

  DECLARE @ErrorSeverity int

  DECLARE @ErrorState int

  SELECT

      @ErrorMessage = N'Error in '+ERROR_PROCEDURE()+N' (Line '+CAST(ERROR_LINE() AS nvarchar(20))+N') : '+ERROR_MESSAGE(),

      @ErrorSeverity = 1,

      @ErrorState = ERROR_STATE()

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

IF @@TRANCOUNT > 0

  COMMIT TRANSACTION

It may not look like much and there are far more sophisticated uses of exception handling, but putting the above code into your stored procedures will increase the quality of your code and the integrity of your database by orders of magnitude for virtually no effort.

Simon Munro