SQL Server Performance Tuning – Proper usage of Temp tables in SELECT query

By Santhosh N

This explains how to use TEMP tables in the SELECT query for better query performance.

While working with multiple tables in the Stored Procedure to fetch the relational data we do require using Temp tables at times. Normally, we tend to use the temp tables something like this in the query

SELECT * INTO #TTable FROM sysobjects

Doing this will cause the exclusive lock on the sysobjects and infact all the system tables, not allowing new objects to be created and accessed from the tables.

The same thing can be rewritten as

CREATE TABLE #TTable(spid int)

And, then

INSERT INTO #TTable
SELECT spid FROM sysobjects

Doing this will not lock the system tables for most of the time.

Related FAQs

This explains the advanced query tuning concepts of the SQL Server in terms of using joins
This explains the QUERY_GOVERNOR_COST_LIMIT option for controlling long running queries in the SQL Server.
This explains where to have a loop if at all required to have one to get the desired result from the database.
This explains how to approach and check for bottlenecks in the SQL queries.
This explains the various precautions needs to be taken care while indexing and choosing what columns needs to be indexed for better performance.
This explains about using functions with Indexed columns in Sql query
SQL Server Performance Tuning – Proper usage of Temp tables in SELECT query  (1143 Views)