Here's a handy little trick.
Have you ever had a big Sql Script, or maybe one that just runs for a long period of time that you have wrapped in begin transaction commit transaction blocks?
Now, if your script fails at some point, do you put in some failsafe mechanism to rollback the transaction and return? It's not an uncommon scenario. But what happens if you want some more detailed information about what went wrong, what failed etc?
How can you, for instance, insert some information or a list of records into a temp table, so that when you rollback the results are still there?
A simple solution is to use table variables. Table variables don't get affected by the transaction state, so if you insert records into these variables, they're going to persist even if you do a rollback transaction
Here's a little example:
begin transaction
--Create the table variable
declare @failedDepartment table (id int, name varchar(255))
--Insert some info about the departments into the table variable
insert into @failedDepartment
(
id,
name
)
select
Id,
name
from
department
--Blow away all the records in the department table
truncate table department
--Prove that there are no records left
select * from department
rollback transaction
--If the rollback affects table variables, then @failedDepartent should be empty
--but it's not
select * from @failedDepartment
As you'll see, @failedDepartments contains records. If you tried doing this with temp tables it won't work, as temp tables get affected by the transaction state.
Happy Sql'ing