Andrej Tozon's blog

Working with the SQL Server 2005 in a past few months, I learned to like many of its new features and improvements, and some of them I'm posting here:

1. SQL2005 noise control - SQL Server 2000 and its implementation of Full Text Searching have been giving me a very hard time. First of, background index updating was slow, which meant you had to wait for a few seconds/minutes before the changed content popped up in the search results. The second thing was the dreaded "Execution of a full-text operation failed. A clause of the query contained only ignored words." error. You would get this error when your search query only included words from the "noise words list". There are some workaround solutions for this one, but none of them are pretty.
However, SQL2005 not only offers faster full-text-indexing [my experience so far is that any changed or added content appeared in the search result list immediately after the change - well, at least from the user experience point of view], but also has a very simple solution for taming the noise words error:

sp_configure 'transform noise words', 1
RECONFIGURE
GO

[from the Books Online: "When the transform noise words option is set to 1, SQL Server replaces noise words with the asterisk (*) in phrase queries."]

2. Common Table Expressions [CTE]: great, especially for working with self-referenced [recursive] queries

3. Error handling with transaction support or TRY...CATCH block with XACT_STATE function: if an error occurs in a TRY part of the TRY...CATCH block, the executing transaction is marked as uncommittable, which is one of the three states that XACT_STATE function can and will return:

• -1 - the session has an uncommittable transaction
• 0 - there is no active transactions
• 1 - the session has an active transaction

Normally, you would check the transaction state with XACT_STATE in the CATCH part of the TRY...CATCH block to properly recover from an error:

• if transaction state is -1 - can do nothing but rollback
• if transaction state is 0 - nothing to do, really
• if transaction state is 1 - there was an error, but the transaction is still committable; so... let's commit

Using TRY...CATCH for transaction control is a much more elegant and readable solution compared to doing this in SQL2000.

SQL Server 2005 holds many more secrets yet to be discovered. The time will show how useful they'll prove to be.