Ajit Goswami-482889 (1/20/2011)
I have a question now on how the Table Variable and Temporary table behaves because while executing a batch statements today i got an issue when i was using a set of table variables.When i used the table variable batch is taking almost 30 mins to complete but when i use temporary table it is getting executed in less than 20 secs.
I am surprised by this result and don't know what is the right solution for me now i want it to have best performance also on the other hand i dont want to get into issues of cleaning up these temporary tables either.
The performance problem is due to the lack of statistics on table variables... they don't have them, and the execution plan that SQL selects to use is frequently less than optimal. Among other things, SQL assumes that there is only one record in the table variable, and there is no knowledge of the density of the data values in the columns.
Temporary tables are automatically cleaned up when the connection is ended. If being used in a stored procedure, they are cleaned up when exiting the stored procedure.
I'd venture a few guesses about your query - how many of these are true?:
1. > 100 rows in the table variable.
2. Joined to other tables.
3. Where clause is utilized on the table variable.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes