Temp tables vs table variables

I am running SQL Server Best Practices on a SQL 2000
database and it is recommending me to change the temp
tables inside SPs to table variables.

I had read already in other places to use table variables
over temp tables. I also know I can't create indexes as
I can on temp tables. Instead I'll have to create either
a primary key and/or a unique index on a table variable.

One question I have is let's say I will be putting thousands
of records in a temp table, should i still choose a table
variable over a temp table for this? Or is there a
recommended limit where if I have to store certain
number of records then it's better to store them in
a temp table rather than a table variable? Or number
of records is not the factor to decide whether to use
temp tables or table variables?

I would like to know when it's ideal or best to use
temp tables instead of table variables and vice versa.

Thank you

On Wed, 14 Dec 2005 13:04:05 -0500, serge wrote:

>I am running SQL Server Best Practices on a SQL 2000
>database and it is recommending me to change the temp
>tables inside SPs to table variables.
>
>I had read already in other places to use table variables
>over temp tables. I also know I can't create indexes as
>I can on temp tables. Instead I'll have to create either
>a primary key and/or a unique index on a table variable.
>
>One question I have is let's say I will be putting thousands
>of records in a temp table, should i still choose a table
>variable over a temp table for this? Or is there a
>recommended limit where if I have to store certain
>number of records then it's better to store them in
>a temp table rather than a table variable? Or number
>of records is not the factor to decide whether to use
>temp tables or table variables?
>
>I would like to know when it's ideal or best to use
>temp tables instead of table variables and vice versa.
>
>Thank you
>

Hi Serge,

There is no hard and fast rule. Testing is the only way to find out for
sure which solution will be best in your situation.

As a rule of thumb, therre tends to be some bias towards table variables
for small amounts of data and towards temp tables for large amounts of
data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)