Approx. size to switch from table variable to temp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Approx. size to switch from table variable to temp

Author

Topic

NoNulls
Starting Member

14 Posts

Posted - 2005-02-21 : 15:48:29

Ok, can someone say what their general rule of thumb is for doing temp tables instead of a table variable..... i.e. 10,000 rows with 4 columns or 1,000 rows with 4 columns....

Just looking for something a little more specific than short tables with little rows...... which is all that I seem to be reading.

Thank you mucho

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-02-21 : 16:05:24

I ~~never~~ hardly ever use table vars unless I am writing a table valued UDF (or whatever they are called now).

I use table vars rareley because:
1. You can't put indexes on a table var
2. The optimizer doesn't treat table vars well
3. I have never seen any significant performance improvement (over temp tables) by using table vars.

I believe most of the information you find out on the web regarding performance of table vars vs. temp tables is BS. I believe that people are simply regurgitating what they have heard, rather than actually testing the performance.

Hopefully microsoft improves table vars in SQL 2005, we'll just have to wait and see.

EDIT:
To answer your quesiton, I use table vars where I might have used a cursor before. I try to keep them small, less than 100 rows and they are never very wide.

-ec

NoNulls
Starting Member

14 Posts

Posted - 2005-02-21 : 17:02:28

Thanks for the reply ec. I haven't had enough experience with table vars to experience any performance difference but I've been trying to use them recently and am having issues with syntax restrictions. I even had a result set difference which really makes me raise my eyebrows. Of course that could just be my inexperience but begesus....

TimS
Posting Yak Master

198 Posts

Posted - 2005-02-21 : 17:24:38

Table vars seem to help keep sysdepends table more accurate that is the only reason I use them some of the time.

Tim S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 15:31:50

You can add a primary key constraint to a table variable, which gives you an index.

Check this example out for details.



DECLARE @TestTable1 table (TableId int PRIMARY KEY CLUSTERED, SomeOtherColumn varchar(50))

INSERT INTO @TestTable1
SELECT 1, 'asdf' UNION ALL
SELECT 2, 'sdffd' UNION ALL
SELECT 5, 'asdsdffsd' UNION ALL
SELECT 10, 'weer' UNION ALL
SELECT 12, 'gsehaseh' UNION ALL
SELECT 77, 'sadgfaehae'

SELECT TableId, SomeOtherColumn
FROM @TestTable1
WHERE TableId > 10

DECLARE @TestTable2 table (TableId int, SomeOtherColumn varchar(50))

INSERT INTO @TestTable2
SELECT 1, 'asdf' UNION ALL
SELECT 2, 'sdffd' UNION ALL
SELECT 5, 'asdsdffsd' UNION ALL
SELECT 10, 'weer' UNION ALL
SELECT 12, 'gsehaseh' UNION ALL
SELECT 77, 'sadgfaehae'

SELECT TableId, SomeOtherColumn
FROM @TestTable2
WHERE TableId > 10

First SELECT query will use a clustered index seek on TableId. Second one does a table scan. With only six rows of data in the table, the query cost is drastic between the two.

Typically the only index that I need on the temp table/table variable is one that is also the PK, so I then just need to decide whether or not a temp table or table variable serves me better for performance.

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-06-02 : 16:35:43

At what point do you decide that you need a PK on the table var?
Most times, I have very few rows in my table variables that I use, like 100 rows or less. I'm guessing if you had thousands, then a PK would be nice.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 16:42:29

I always put one on my table variables, that's if I have a column that makes the row unique. I hate seeing a table scan in my execution plans even with 6 rows.

You can't put a PK over more than one column when using a table variable, so that's the drawback.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-02 : 18:58:42

One of the great SQL Server Myths lives!!!!

A table variable WILL create objects in tempdb... regardless of any size...
A table variable supports just about every constraint a normal table can..CHECK, Composite keys, Multiple Candidate Keys.. FK's are one exception

Personally, I always use temp variables over temp tables.. about the only exception is caused by the INSERT..EXEC limitation on variables... I can't remember NOT creating a key on a table variable in production quality code...

Need more...

1) Fire up profiler.. Filter for tempdb (Usually DB = 2), Add OBJECTID, OBJECTNAME, OBJECTTYPE to the column output.. Add "Object: Create" to the Events..
2) Run this...


USE PUBS
GO
declare @Set TABLE (ID INT NOT NULL, A INT IDENTITY(1,1) NOT NULL CHECK (A > 0)
	, ADate DATETIME DEFAULT(GETDATE()), PRIMARY KEY(ID, A))

declare @Bag TABLE (ID INT NULL , A INT NULL )

CREATE TABLE #TempTest (ID INT NOT NULL, A INT NOT NULL,
	PRIMARY KEY(ID, A))

Insert @Bag (ID, A)
Select Nx.ID,X.ID+1 from sysobjects Nx cross join sysobjects X
where Nx.ID < 10

Insert @Set (ID)
Select Nx.ID  from sysobjects Nx cross join sysobjects X
where Nx.ID < 10

Insert #TempTest (ID, A)
Select Nx.ID,X.ID+1 from sysobjects Nx cross join sysobjects X
where Nx.ID < 10

DROP TABLE #TempTest

DavidM

A front-end is something that tries to violate a back-end.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 19:03:11

I could've sworn I got an error when I tried to put a PK over more than one column on a table variable. I obviously was wrong.

I too use table variables more often. My working table usually has less than a couple thousand rows in it.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-06-02 : 19:13:38

there are no optimizer statistics on table vars, temp tables have statistics.

What is the benefit in using a table var over a temp table (other than when used in a table valued function)?

-ec

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-02 : 19:31:04

>> What is the benefit in using a table var over a temp table

Tighter scope..assured destruction.. lighter impact on transaction logs..less compilation in procs..

DavidM

A front-end is something that tries to violate a back-end.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-06-02 : 22:58:15

quote:
Originally posted by byrmol

>> What is the benefit in using a table var over a temp table

Tighter scope..assured destruction.. lighter impact on transaction logs..less compilation in procs..

DavidM

A front-end is something that tries to violate a back-end.

Thanks for the info, I was unaware of the thresholds for recompilation of stored procs.

I googled around and found this blog entry which seemed pretty informative. http://odetocode.com/Articles/365.aspx

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources