SQL Tips

I'm reading a book on SQL Server at the moment, I just thought I would make a few notes as I go:

Exceptions should be thrown when the function or procedure is in a state that is outside of its handlable states - it is in exception. I don't think that I see code throw exceptions often enough. If it can't handle that state just throw (Or RAISERROR).

In order to make a statement, parsing and scope-resolution exception behave like batch exceptions - and hence terminate the current batch (to the next GO statement) use:
SET XACT_ABORT ON
I can see that this might be useful with a transaction (Hence the name "Transaction Abort" = XACT_ABORT). I have seen that a number of our update statements are running all the way through when really an exception should be thrown and the batch aborted and rolled back. Now that I know about it I'm sure I'll find more uses.

In SQL Server error messages less than 11 are minor, 11 to 16 are serious, and greater than that you better speak to a DBA. User errors triggered by the RAISERROR function are warnings if they have a serveritiy less than 11, while normal is 11 to 18, and 18+ are severe. Over 18's can only be called by members of the sysadmin role. 20 and over cause the connection to break, be careful!

RAISEERROR messages can be formatted similar to String.Format (actually it's much closer to printf in C) using %i (integer) and %s.
For example: RAISEERROR('Problem with FilmId: %i', 16, 1, @FilmId )

Row numbers are from the last GO statement, which is obvious once you know that GO is not a T-SQL command, its a directive to send the commands to SQL Server serially.