Editor's Note: Send your SQL Server questions to Richard Waymire, lead program manager for SQL Server development at Microsoft, at firstname.lastname@example.org
When I run batch processes in SQL Server 7.0, the first process consumes almost all the server resources, causing the second process to advance very slowly. Is this typical SQL Server behavior?
If the first batch process is the only process running, it takes all the power it can to resolve a query. However, as you add more processes, the available resources usually spread out equally among them. Process speed also depends on whether you have multiple CPUs and whether a parallel query is running at the same time. Generally, parallel queries continue to use the resources that were available on the system when they started until the query is complete.
When I reference a small temporary table in the stored procedure I created it in, I don't experience performance problems. But when I call another stored procedure and it references that temporary table, performance takes a dive—it's maybe 100 times slower. I have to use variables or parameters to hold the values I want to reference between stored procedures. Is this a known problem?
Yes. Microsoft has written an article that addresses the problem in SQL Server 7.0. The article says if you reference a temporary table in a stored procedure other than the one that created the temporary table, you risk recompilation of the stored procedure every time you execute it. See "INF: Troubleshooting Stored Procedure Recompilation" at http://support.microsoft.com/support/kb/articles/q243/5/86.asp.
I need to select rows from one table and insert them into a temporary table that lists ZIP codes. The number of ZIP codes varies, so I need to wrap the SELECT statement into an EXEC statement, which resides inside a stored procedure. When I execute the EXEC statement, then try to access the temporary table, I get an Invalid Object message. However, when I print the SQL statement and paste it into the stored procedure, everything works fine. Why can't I access the temporary table when I create it by using the EXEC statement?
The answer to your question is in SQL Server Books Online (BOL) under the Create Table statement. SQL Server drops the temporary table when your execution context leaves the stored procedure. You might consider using a global temporary table. The behavior you describe is occurring for a specific reason. If you create a local temporary table in a stored procedure or application that several users can execute at the same time, SQL Server has to be able to distinguish the tables that the different users create. SQL Server distinguishes the tables by internally appending a numeric suffix to each local temporary table name. A temporary table's full name, which is stored in the sysobjects table in tempdb, consists of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow room for the suffix, the table name specified for a local temporary name can't exceed 116 characters. SQL Server drops temporary tables when their names are longer than 116 characters unless the stored procedure uses DROP TABLE to explicitly drop the table.
SQL Server also automatically drops a local temporary table created in a stored procedure when the stored procedure ends. Any nested stored procedures that the stored procedure that created the table executes can reference that table. However, the process that called the stored procedure that created the table can't reference the table. SQL Server drops all other local temporary tables automatically at the end of the current session.
SQL Server also drops global temporary tables when the session that created the table ends and all other tasks have stopped referencing them. SQL Server maintains the association between a task and a table only for the life of one T-SQL statement. Consequently, SQL Server drops a global temporary table at the completion of the last T-SQL statement that was actively referencing the table when the creating session ended.