*slams head against wall*

Always tempdb.

The buffers might be in memory, but they're still tempdb buffers.

No, that's incorrect. Implicit transactions create an implicit multistatement transaction. But single statements still have their own implicit transactions. Take this batch, for instance:

INSERT Tbl VALUES ('w',...

Every table (of any kind) exists in RAM until it is flushed to disk. If the lifetime of a table variable (or temp table, or even permanent table) is...

Good question.

I constantly battle the in-memory myth...

Which one?

There is ALWAYS a transaction whenever any kind of data modification occurs. Each DML statement starts an implicit transaction. You can choose to enlist one or more DML...

I found the whole setup to be somewhat of a red herring. The point is, if the trigger fails everything is going to fail. Doesn't matter if there...

I suppose the attitude is, if it's not in the Standard, it's proprietary

Possibly. There are both tembdb contention issues and recompilation issues to worry about.

You can greatly reduce tempdb contention by following this KB article:

http://support.microsoft.com/kb/328551

As for the recompilation, you can...

Salvor,

You're right, they are set-based. But when a lot of people talk about set-based solutions (see posts by certain "gurus"), they show disdain for temp tables because they're proprietary....

Why do temp tables "suck"?

Purely set-based solutions are not always the best in terms of performance. There is a time and place for everything -- even cursors have some...

I think you can be granted access to the view. AFAIK it does not require access to any fixed server role.

This is a really great potential interview question! Thanks.

SqlFunctionAttribute is an attribute that marks a method as a function -- so that SQL Server knows how you intend to use the method.

For more information on attributes, see:

http://insight.zdnet.co.uk/software/applications/0,39020466,2118655,00.htm