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)