Top Features for SQL 2005

Monday, December 20, 2004

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.“

Before After
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.


Comments
Raymond Lewallen Monday, December 20, 2004
I couldn't agree more. Another thing I'm looking forward to is one single little word: Pivot. How much code is that going to save? A lot. I personally think most DBAs should be cautious about adding assemblies to the server without inspecting the code first. Application developers aren't going to like DBAs reviewing their C# code, but DBAs aren't going to like application developers creating bottlenecks all over the place. I discussed this briefly on my blog last week under some post titled something like &quot;Creating a stored procedure in VB.Net for SQLCLR&quot;.
Darrell Monday, December 20, 2004
Actually in the first example it's easier to use goto's. I know most people think goto's are evil incarnate, but for stored procs they rock much more than repeated code!
<br>
<br>The only caveat is you have to make SURE to return before the goto label, otherwise the stored proc will execute the goto at the end as well. And since it usually has the rollback statement, you get a &quot;trancount is already zero&quot; error message or some such nonsense.
Darrell Monday, December 20, 2004
But I agree, it will rock!
<br>
<br>Another favorite feature is the row number, so I can select rows 11-20 ORDER BY Name DESC. It will make custom paging for a datagrid super simple. And it is one more reason NOT to go to Oracle! :)
Scott Allen Tuesday, December 21, 2004
Darrell:
<br>
<br>I didn't know about row number, that will be SWEET!!!
<br>
<br>Raymond, Darrell: dotnetjunkies seems to be out of disk space this evening! I can't reach either of your blogs!
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!