Local Temporary Tables and Table Variables

Question

Local Temporary Tables and Table Variables

peterhe

SSChampion

Points: 11469
More actions
August 31, 2008 at 11:17 pm

#189908

Comments posted to this topic are about the item Local Temporary Tables and Table Variables

Viewing 15 posts - 1 through 15 (of 34 total)

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

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

Very nice.

Just one small thing. You say that for data storage, temp tables are in TempDB and table variables are in memory and tempDB. I may be misunderstanding what you mean there, but temp tables are also memory resident unless it becomes necessary to write them to disk (memory pressure, too large a table)

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

humbleDBA Hall of Fame Points: 3490 More actions · Answer 2

Agree with Gail, nice article, plus I'm reading it that you're suggesting that Data Storage for Temp Table is not in memory - for which it is in-memory unless pushed out to disk.

Also, I'm taking it that, as this article is not an old one, that it covers SQL2005. If this is the case, then INSERT...EXEC is supported for Table Variables. You can test this with the following:

-- SQL2005

DECLARE @helpdb TABLE (name sysname, db_size nvarchar(13), owner sysname, dbid smallint,

created nvarchar(11), status nvarchar(600), compatibility_level tinyint)

INSERT @helpdb (name, db_size, owner, dbid, created, status, compatibility_level)

EXEC dbo.sp_helpdb

SELECT * FROM @helpdb

HTH

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

Awesome article that covers some of the lesser considered aspects of temporary tables and table variables. Thank you.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

David Quéméré SSC Rookie Points: 27 More actions · Answer 4

The 2nd chapter about UDTs contains a mistake : the script supposed to raise an error and the workaround don't have any difference. I guess the error script should use a UDT (dbo.Name) instead of a native type (nvarchar(50)).

Thanks to all the authors and SqlserverCentral for their efforts and the useful documentation they bring us.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Good article!

You did forget to mention one workaround for the UDT's that would allow it to work across all databases including TempDB... Define the UDT in the Master DB. Of course, most folks will argue that the Master DB is a sacred entity that should not be touched and, for the most part, I agree. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Of course we use a startup procedure to define the UDT's in TempDB.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Anipaul SSC-Insane Points: 24681 More actions · Answer 7

Anipaul

SSC-Insane

Points: 24681

September 1, 2008 at 11:16 pm

#865830

Wonderful article..

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

rbarryyoung (9/1/2008)
Of course we use a startup procedure to define the UDT's in TempDB.

Does defining them in model work as well?

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 9

GilaMonster (9/1/2008)
rbarryyoung (9/1/2008)
Of course we use a startup procedure to define the UDT's in TempDB.
Does defining them in model work as well?

According to this article, yes

http://www.sqlservercentral.com/articles/Advanced/3104/

Best Regards,

Chris Büttner

peterhe SSChampion Points: 11469 More actions · Answer 10

To humbleDBA,

You are right. INSERT ... EXEC works for table variable too in SQL Server 2005. In SQL 2000, it does not work. Thanks.

Jeff, Gail, and Chris,

Thanks for the workarounds for UDTs in master and msdb. I have not had a chance to test and confirm it.

David Quéméré,

Your are right. The script has a mistake. It's actually the workaround script. I guess I messed it up by copy/paste

Thank you guys for taking time to read/comment on it

paul.t.silvey SSC Enthusiast Points: 175 More actions · Answer 11

hmmm... I personally hate table variables because they cause the disk queue length (I/O) and CPU to shoot through the roof. They only work on simple small systems with low transactional volume. If you take a real life OLTP system or a large warehouse, they are pretty much useless in my book. They seem to bring the system to its knees. They kind of remind me of Cursors... wait... aren't cursors memory intensive also? Do you see the similarities?

I will take an indexed temp table over a table variable any day on a heavy usage system.

SQL Silvey

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

Probably because of the affect they have on queries. Bad cardinality = bad query plans = higher than expected IO and CPU.

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 13

I have a question, hopefully you guys will know better than me

We use a lot of TABLE VARIABLES (not temp tables) in our select statements for our heavy traffic websites

Server has 4GB RAM. Windows 2003 Standard x64, SQL 2008 RC0 64-bit

SQL Server has been limited to max of 3GB memory, with 8GB page file

Now the issue is our page file usage in Task Manager kept growing

In 1~2 hours it has grown from 4.2GB to 9.5GB and not letting go so it seems

There is no LONG running queries (no long duration, no high CPU)

Probably a matter of time before it may reach the 12GB commited charge (4 physical + 8 page file)

But does table variable get dropped??

Noticed the article says "Current Batch" scope, while temp table is "Current Session"

what is defined a batch? what if the front end .Net keeps re-using the same connection, therefore not dropping the table variable?

Appreciate any help

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

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

Table variables has the same scope as normal variables. Much less than a temp table. Their lifetime is limited to one set of commands (batch) sent to the server.

So, if you send a bunch of ad-hoc SQL to the server (using Execute or ExecuteNoQuery or the like) and that ad-hoc SQL declares and uses a table variable, the table variable only exists for the duration of that piece of ad-hoc SQL.

Are you running SQL Enterprise or Standard?

Have you checked to see if the page file usage is coming from SQL or from something else on the box?

Lastly (not a perf issue) RC0 isn't licensed for production usage (unless you're a member of the early-adopter program) and has a built-in expiry date. You should replace that with SQL 2008 RTM as soon as you can.

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