SQL Server 2000 table data type

[MarcusH]

Hi,

I've seen that the new 'table' data type in SQL Server 2000 is a better option than temporary tables because the data is kept in memory for the duration of the query (stored proecedure or batch).

Although this sounds good to me as far as speed goes, I'm a bit concerned about memory.

If I have a stored procedure that is being used by a lot of concurrent users, it seems obvious that I will eventually run into memory problems.

Has anyone experienced anything like this? Are the resources used to set up the temp table still more of a hit on the server than the equivalent amount of activity on local tables?

Thanks
Marcus

 

[tlbroadbent]

Here are some other reasons (from SQL BOL) to use table variables.

Table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

Table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.

Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.

In addition, table variables reside in the current database rather than tempdb like temporary tables. This can be advantageous on servers where applications make heavy use of tempdb. Also, table variables are perfect for use in User Defined Functions.

One concept that gets a lot of press is "table variables reside in memory and temp tables reside on disk." This is generally false. Unless temp tables are very big, they will reside in the cache buffer, which is in memory. When table variables are very big, data may be written to disk.

In theory, table variables are great! In practice, temporary tables may out perform table variables in many applications. In the tests that I have done, temporary table performance has been slightly better than table variables except for some large data sets. In one test, I loaded 1.6 million rows to a temp table and a table variable. The overall performance was slightly better using the table variable.

One limitation of table variables worth considering is that indexes cannot be created on table variables. You can add a Primary key and unique constraint when creating the table. However, you cannot use Create Index to add additional indexes. I find performance improves if indexes are built on temp tables after inserting data rather than before. This cannot be done if table variables are used. I haven't done much analysis of this issue yet so I don't know if it will be a major factor.

I plan to make more use of table variables in the future. I think there is a lot of potential despite the drawbacks.

Hope this helps. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MarcusH]

That helps a lot. Thanks very much for that. Plenty of food for thought.

Marcus