From: Robert Klemme <shortcutter@...>
Date: 2006-12-23T01:30:09+09:00
Subject: Re: string of strings...

On 22.12.2006 01:34, Sam Smoot wrote:
> Keep in mind that temp-tables in MSSQL are still written to disk in
> TempDB. So as a general rule, if you're concerned about performance, if
> you don't *need* a temp-table, don't use it.

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.

> Not that performance should always be a #1 concern of course. Just that
> I've seen hundreds if not thousands of stored-procedures that use
> temp-tables as a matter of course just because the developer wasn't
> comfortable with sub-selects, grouping, etc. I'm sure you won't fall
> into that trap though. :-)

Yeah, that's what I heard also: often people use temp tables because 
they do not know SQL and the capabilities of their RDBMS very well.

> 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.

Kind regards

	robert


PS: Please don't top post.