Creating temporary tables and declaring tables as variables

Question

Post reply

Creating temporary tables and declaring tables as variables

gacanepa

Old Hand

Points: 352
More actions
May 20, 2017 at 11:13 am

#330370

Hi everyone,
I still consider myself a newbie, so please bear with me.
While going through some existing stored procedures, I've noticed that some people create temporary tables (say #MyTable), while others seem to prefer declaring them as variables (for example, DECLARE @TestTable AS TABLE), and then proceed to list the fields and the related data types.
That said, are there any differences / advantages / disadvantages between these two approaches? I've only noticed that WITH(NOLOCK) is not allowed in the latter, while it is in the former. Not sure if that is significant, but that's as far as I've gotten so far.
Any tips or ideas will be more than welcome.
Thanks in advance.

Viewing 8 posts - 1 through 8 (of 8 total)

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

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

Well, nolock's a bad idea, so you shouldn't be judging based on that.

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

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

gacanepa Old Hand Points: 352 More actions · Answer 2

@GilaMonster,
Thank you so much! That was exactly what I was looking for.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

I've seen Wayne's presentation on table variables versus temp tables. It's a good one. Now I'll have to read the article. 😉 Thanks, Gail.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) Use Temporary Tables in EVERY situation that you can. I my 45000 or so hours of SQL Server experience and training, the situations where you cannot are pretty rare edge cases.

2) Never use Scalar or Multi-statement User Defined Functions (one place you must use Table Variables).

3) Reread items 1 and 2 😎

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Hot off the presses, and quite appropriate for this post:

Paul White on Temp Object Caching

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

gacanepa Old Hand Points: 352 More actions · Answer 6

Thank you everyone for taking the time to share your experience and the useful links!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 7

In my opinion, this question already has one of the best internet SQL Server answers I've ever seen.

I'd highly recommend reading Martin Smith's answer to this at https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server

That should give you ample comparison/contrast to chew on.

Cheers!