Which is faster when using SQL Server 2000 or 2005, temp tables or the table datatype?

Generally speaking, if the data you are dealing with is not large, then the table datatype will often be faster than using a temp table. But if the amount of data is large, then a temp table most likely will be faster. Which method is faster is dependent on the amount of RAM in your server available to SQL Server, and this of course can vary from server to server. The greater the available RAM is in a server, the greater number of records that can be efficiently stored in a table datatype. You may have to test both methods to determine which method is best for your situation.

Here are some reasons why the table datatype, when used with reasonable amounts of data, is generally faster than using a temp table:

• Records are usually stored in memory, not in a temp table in the tempdb database, so performance is much faster.
• Table variables act like local variables and have a well-defined scope. Whenever the batch, function, or stored procedure that created the table variable goes away, the table variable is automatically cleaned up.
• When a table variable is used inside a stored procedure instead of a temp table, fewer recompilations occur, reducing server overhead.
• Table variables require less locking and logging resources when compared to temporary tables, reducing server overhead and boosting concurrency.

If you haven't learned how to use table variables yet, you need to take the time to do so as soon as you can. They can be powerful tools in the correct situations.