Home » Using Temporary tables and Temporary variables instead of Cursors

Using Temporary tables and Temporary variables instead of Cursors

Mostly in production you might want to consider using a temp table is to avoid having to use a cursor. SQL Server cursors have huge overhead and slow SQL Server’s performance. One alternative of using a cursor is to use a temp table instead. In almost all cases, using a temp table over a cursor will produce less overhead and better performance.
SQL Server offers a data type called table which can be used for the temporary storage of a set of rows. A variable, of type [table] behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECT, INSERT, UPDATE, and DELETE can all be made against a table variable.
If you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them faster in some cases.
If you have no choice but to use a temp table, you can help to optimize its performance by taking one or more of the following steps:
• Only include the columns and rows you actually need in the temp table, no more.
• Do not use SELECT INTO to create your temp table, as it places locks on system objects. Instead, create the table using standard Transact-SQL DDL statements, and then use INSERT INTO to populate the table.
• Consider using clustered and non-clustered indexes on your temp tables, especially for very large temp tables. You will have to test to see if indexes help or hurt overall performance.
• When you are done with your temp table, delete it to free up tempdb resources. Do not wait for the table to be automatically deleted when the connection is ended.
• If the tempdb database is not already on its own dedicated disk or array, consider taking this step. By isolating the tempdb database on its own disk, disk contention is reduced and performance is increased.
Method of using Temporary Table or Table variable:
Create a temporary table with a extra column(say rowed) with identity function(1,1)

Create table #test
(
TestID int,
Dept varchar(50),
Rowed int identity(1,1)
)

Declare a table variable with a extra column(say rowed) with identity function(1,1)

DECLARE @test TABLE
(
TestID int,
Dept varchar(50),
Rowed int identity(1,1)
)

Set a variable, say @rowcount with select min(column added above) from table_name
Set a variable, say @i with select count(*) of the table_name
set the initial values as zero for any other int or float variables used.
Use a while statement as

While @rowcount<[email protected] Begin Set each variable with corresponding values in this set Example: Set @sales=(select sale_frn from Table_name where [email protected] and sales_frn is not null) Set @year=( select year from Table_name where [email protected] and Year is not null) The work with above derived variables and various set based operations can be performed.

Leave a Reply

Your email address will not be published. Required fields are marked *