Hi

In my earlier post I talked about the how we can work with the table variable in SQL Server 2000. But we also have temporary tables in SQL Server 2000 to work with tables. In this post I will discuss the difference between temporary tables and table variables

In SQL server 2000 we cannot use the table variable as an input or output parameter. The table variable is scoped to be stored procedure, batch or any user defined function like local variables. The variable do not exists after the stored procedure exists and hence no need to clean up with a drop statement. But the table variable can be used to return from a user defined function.

Table variable use fewer resource than a temporary table because of there limited scope. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead. This is also gives better performance to the table variable as against the temporary table.

We can also declare constraint, primary key, identity columns, and default value in the table variable very easily. SQL server does not maintain any statistics on the table variable [Remember statistics are heavily used by the query optimizer to determine the best method to execute].  

Also we cannot change the definition of the table after the table is created. This means that we cannot use the alter table statement on a table variable. If we are using a table variable in a join, you will need to alias the table in order to execute the query.

Hope this helps
Thanks
Vikram