PURPOSE OF TEMPORARY TABLES

Question

PURPOSE OF TEMPORARY TABLES

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

tsje (2/16/2011)
What about issues of memory usage on temp tables or table variables.
Is there a difference?
i.e. does a temp table (#mytable) write to the disk in tempdb?
vs. a table variable (@mytable) which I believe is stored in RAM or the pagefile if needed?
Is the amount of this memory usage significant?
Should it be a consideration depending on the size of the temp table being used?
Thanks in advance... TS

They both work the same way with regard to memory vs disk, per Microsoft and extensive testing.

Yes, there are performance issues that need to be taken into account when using either.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 2

You need to check out Gail's blog. She has a very interesting discussion on this one:

~~http://www.sqlservercentral.com/Forums/Topic415829-338-1.aspx~~

EDIT: Whoops. The first link was wrong.

http://sqlinthewild.co.za/index.php/2007/12/19/temp-tables-and-table-variables/[/url]

http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/[/url]

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

tsje SSC Journeyman Points: 88 More actions · Answer 3

Thanks Brandie,

Both articles were awesome and in depth.

Each led to other articles that helped greatly.

This is my first post here and I must say, I'm very impressed!!!

Thanks again,

Mad Myche SSCommitted Points: 1593 More actions · Answer 4

In regards to temp vs variable tables, when I am building procedures that could use either I will develop using both methods and check the performance on both instances.

Director of Transmogrification Services

GSquared SSC Guru Points: 260824 More actions · Answer 5

Mad Myche (2/16/2011)
In regards to temp vs variable tables, when I am building procedures that could use either I will develop using both methods and check the performance on both instances.

That's valid so long as the data will stay relatively static.

Temp tables generate stats on the data in them, so volatile data often works better in them, but static data in a dev environment won't show that during unit tests.

You should be able to catch that kind of thing in production, if you monitor query performance reasonably well.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I advise my clients to only use table variables in 2 (pretty uncommon) scenarios:

1) VERY high call volume sprocs where extra recompilations from temp tables may be an issue

2) VERY large sprocs that have multiple DML activities against temp objects and again the recompiles could be an issue. But even in this case there are exceptions, especially if you can have either widely disparate rowcount hits from joins to said temp object or widely disparate numbers of rows in said temp object. Those nested-loop-joins and/or index seek/bookmark lookup operations over kajillions of rows are a crushingly bad performance issue.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

The last posted comment was indeed too funny!!! Hehehe.

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Temp tables are used for interim calculations, where we actually can't make logical calculation using the actual tables.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

The other two cases I will use table variables are:

1) When the table variable is not joined to any other tables for the final query. Maybe it's used to dump a stored proc output, do a couple updates and then select that.

2) Error logging table. Purely because table variables ignore explicit transaction rollbacks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

GilaMonster (2/16/2011)
The other two cases I will use table variables are:
1) When the table variable is not joined to any other tables for the final query. Maybe it's used to dump a stored proc output, do a couple updates and then select that.
2) Error logging table. Purely because table variables ignore explicit transaction rollbacks

Good ones Gail. I need to add those to my normal spiel.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 11

GilaMonster (2/16/2011)
2) Error logging table. Purely because table variables ignore explicit transaction rollbacks

That is probably the best reason for using table variables that I've ever seen. I'll have to let our Devs know about this one.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.