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.

 

Data Type, The most neglected feature of SQL Server

Oktober 7, 2014

I have been developed apps for 10 years using Microsoft SQL Server.  One of the problem i often found in my journey is a not consistent data length for same meaning data expecially if you used varchar and nvarchar. For example :

  • At table customer , you set picemail as varchar(50) while at employee you set employee email as varchar(255).
  • At table order, orderno is being set as varchar(10) while at orderdetail the field orderno is being set as varchar(20). Assuming that you not set foreign key constraint this could happen.

These could lead into problems if you don’t address. For instant if employee email is longer than 50 character then when you assign to picemail at table customer, an error will be occured

Another problem is data conversion. If you join table order and table orderdetail using orderno there will be data type conversion from varchar(10) to varchar(20). This will cut performance down up to 60%. You can read it further at :

It is as simple as create type EmailType From varchar(50) to ensure both problems goes away from the beggining.

The benefit of using this feature, in my opinion are :

  • you have a standarised data type
  • you will have no implicit data conversion
  • New member of you team will learn easly