Archive for the ‘SQL Server’ Category

Tabel Variabel Vs Temporary Table, When To Use

Oktober 15, 2014

As we know, on the sql server 2008 there is a new feature which is table variable.

This powerfull feature offers us a new data type which behave like temporary table but it is automatically disposed after the scope goes out. So why Microsoft created this new stuff  while there is temporary table ?

There are a few issues with previous release of sql server which are :

* there is a chatty operation when sending humogous data to the sql server from application, since the basic insert statment only accomodate 1 datum/tupple every time its connect.

* there is performance hit because of the using of temporary table. Temporary table statistic is being managed. Every 500 new rows being inserted to temporary table there will be a trigger to calculate statistic. If this happen the current stored procedure will be recompile. This behaviour was made by design.

*Temporary table need to be manually clean up

In order to overcome this difficulties than the new datatype was proprosed. Evert soluttion is another problem. This table variable lacks of :

* index beside primary and cluster there is no way to define index

* statistics. Optimizer will not able to choose good plan. It always assume the size of table is 1 record

* if the type needs to be altered all stored procedures which use the type need to be drop first.