From: Sam Smoot <ssmoot@...>
Date: 2006-12-23T06:00:05+09:00
Subject: Re: string of strings...

> The fact that they reside in tempdb does not necessarily mean they are
> written to disk or that they are slow.  Small temp tables will easily
> fit into the page cache.  And since they are created directly before
> usage likelihood of finding those pages in the cache is pretty high.
> Also, tempdb has recovery model simply which reduces burden on the disk
> somewhat.  Also, I am not sure whether operations on temp tables are
> logged at all.

I think you've got it right about whether a temp-table _must_ be
written to disk during it's lifetime. Looking it up just now I found
conflicting accounts, so I can't authoritatively say either way. :-/

> > There are also in-memory tables, but I don't remember the caveats. I
> > think perhaps they might have a global scope or something inconvienent
> > like that, but don't quote me on that.
>
> They are called "table variables".  Scope is not an issue, they are
> scoped like local variables (unless maybe if they are returned from a
> procedure).  IIRC limitation is that they do not allow indexes and
> constraints.

It looks like I was actually thinking about global temp-tables using
the ##table syntax. Shows how often I've found a use for them I
suppose. :-)

> PS: Please don't top post.

Sorry about that. :-o