Using temporary tables, table variables and CTE in SQL Server 2005

Some of the SQL queries may require a temporary holding place in the memory for doing some logical process.
For example, select certain number of rows from a table and remove/update some rows within this based on some condition.
This may require a temporary variable or table in SQL server 2005 to achieve this task.

Here are the few ways to store the data in temp tables for processing

1) Temporary Table Variables

It is a good idea to create a temporary variable if the data rows you are going to store is less than few 100 rows.
This has some limitations like cannot create index etc...

example of a temporary table variable use


Code:
declare @tempTable table(sno int identity,name varchar(55));

Insert into @tempTable(name) values ('james');
Insert into @tempTable(name) values ('peter');
Insert into @tempTable(name) values ('Rusell');

select * from @tempTable;


2) Temporary Tables


If you need to store a large data to a temp location, it is good idea to store this in a temporary table rather than on a table variable.

example for creating a temporary table

Code:
create  table #tempTable(sno int identity,name varchar(55));

Insert into #tempTable(name) values ('james');
Insert into #tempTable(name) values ('peter');
Insert into #tempTable(name) values ('Rusell');

select * from #tempTable;




If you want to create a global temporary table which can be accessed across the connections, use ## to create this.
example, ##globaltemtable



3) Derived Tables

You can use a derived table to hold data temporarily in SQL server.
Derived table is a result set of a subquery.

Example,

Code:
select sno,name from (select * from people where age>20) as temppeople



This derived table temppeople can be used only once in a query.


4) Common Table Expressions (CTE)

CTE is a temporary result set object which can be used the duration of the query.


Example,

Code:

with TempCTE(sno,name)

as
(
select sno,name from testtable where name like 'A%'
)

Select * from  TempCTE


CTE can be used for buliding the recursive query and store the result in one result set.

A CTE can be used many times in same query.

Example,

Code:

select * from people inner join TempCTE on  people.sno= TempCTE.Sno
                     leftjoin peoplerol on  peoplerole.sno= TempCTE.Sno

Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'ad...
Lire la suite

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'a...
Lire la suite

Database connection pooling in ADO.Net

Imagine a situation that multiple users are connecting the same database through our application. In an application establishing a database connection and logging into it is a time consuming activity. It needs several seconds for every time it establishes a connection. So opening and maintaining a database connection for each user is not an ideal solution. When the number of users increases, and if the system�s memory set is limit, probably it leads to a system crash. This problem can solve through connection pooling. A connection pool is a cache or pool of database connections maintained by the application so that the connections can be reused when the database receives future requests for data. Establishing once and reserving it for future request can essentially improve the overall performance of the application. In connection pooling, after a connection is created, the connection object is placed in the pool. When a simultaneous connection request is made later, ...
Lire la suite