Re: Need SQL Server Temp Table equivalent (challenge!)

As a Sybase/SQL Server/Oracle DBA, I have to disagree with you. There are indeed certain things that are more intuitive in SQL Server than in Oracle, but the reverse can be said, too. Ask a SQL Server DBA to write a good script to show disk space usage and free space available.

Now back to your #temp table issue: those who use #temp tables typically have a bad design in their application, or they don't know how to do SET operations, or they don't know how to effectively use cursors, or all of the above. For every application that I helped move from Sybase/SQL Server to Oracle, all the neccesity of #temp tables are gone!

Why is #temp table bad? These tables are created in the tempdb, and let's say you have an application used by 2000 users (by the way, this is probably not very often seen in SQL Server), and each user dynamically create then drop their #temp tables. Now my dear SQL Server developer, where is the bottleneck? Remember that tempdb is also used for order by and group by, and if you have hundreds of #temp tables there, you'll need large tempdb, too. And worse still, if your tempdb is filled up, the whole database hangs.

Sybase (whose source code MS shamelessly copied) realizes the problem, and now makes it possible for a database instance to have multiple tempdb's, but SQL Server still doesn't have a clue. Hopefully SQL Server developers realize that also and give some consideration to performance: don't simply get tempted by "simply and intuitive" solutions. Poor DBAs will pay for your poor design. Thanks
Jining Han Received on Sun Jan 23 2005 - 10:37:28 CST