Top Features for SQL 2005

The CLR gets all the press in articles about SQL Server 2005, but I think the most popular features won’t involve CREATE ASSEMBLY. The most popular features will be those that prevent ‘uncomfortable conversations’. You know, those conversations you don't want to hear....

Feature: DDL Triggers
Prevents: Well I thought I was in the test database when I dropped the Orders table, but, um, well, um. We have a backup right?

Feature: Snapshot Isolation
Prevents: Hi, I’m Sandy with the Oracle Corporation. Your application will suck wind with with their database. Pick us. We can do things they can’t do, blah blah blah multi-version consistency blah blah blah. I’m not leaving till you pick us.

Feature: TRY/CATCH in T-SQL
Prevents: “This stored procedure has more litter in it than the Hudson river.“

BEGIN TRANSACTION
  
  UPDATE Categories SET CategoryName . . .

  SET @Error=@@ERROR
  IF @Error <> 0 BEGIN
    RAISERROR('blah blah blah',16,10)
    IF @@TRANCOUNT <> 0 ROLLBACK
    RETURN(@Error)
  END

  INSERT INTO Categories . . .

  SET @Error=@@ERROR
  IF @Error <> 0 BEGIN
    RAISERROR('blah blah blah',16,10)
    IF @@TRANCOUNT <> 0 ROLLBACK
    RETURN(@Error)
  END

COMMIT TRANSACTION

BEGIN TRY

  BEGIN TRANSACTION
    UPDATE Categories SET CategoryName . . . 
    INSERT INTO Categories . . . 
  COMMIT TRANSACTION 

END TRY

BEGIN CATCH
  IF @@TRANCOUNT <> 0 ROLLBACK
  SELECT @Error = ERROR_NUMBER()
  RETURN (@Error)
END CATCH

Yes, nice indeed.