Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need SQL Server Temp Table equivalent (challenge!)

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

From: Jining Han <jining.han_at_gmail.com>
Date: 23 Jan 2005 08:37:28 -0800
Message-ID: <1106498248.925863.186230@z14g2000cwz.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US