10774 - temporary tables or type table variables

Hi Experts

It is best temporary tables or type table variables
enrique_aeoAsked:
Who is Participating?
 
Duy PhamFreelance IT ConsultantCommented:
Dmitry Tsuranoff has made a quite detailed comparison.
0
 
ste5anSenior DeveloperCommented:
Well, it depends on the use case...

Just a thought to your questions: Wouldn't you as Microsoft remove those possibilites, when they were always bad?
0
 
Éric MoreauSenior .Net ConsultantCommented:
They have different features for different needs.

If your have just a few rows (couple of thousands) and only need a one field primary key, the table variables are a better choice.

Most of other needs are better deserved by temp tables.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
enrique_aeoAuthor Commented:
thanks experts

can you give me an example in T-SQL code
0
 
Éric MoreauSenior .Net ConsultantCommented:
an example of what?

The easiest is probably:
DECLARE @T TABLE(I INT);
INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)
INSERT INTO #T VALUES(1),(2),(3),(4),(5)

SELECT * FROM @T 
SELECT * FROM #T

DROP TABLE #T

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
Jim, have you gone through the link? It was very exhaustive. And copying it here would be a simply plagiarism. I just don't understand that rule when the links is for a good reason.
0
 
Duy PhamFreelance IT ConsultantCommented:
@Éric:  Really sorry about that. I should have known about it before posting such a 'blind link', but I will surely and carefully read through the Terms of EE again to avoid that in the future.

I just thought that link did provide kinda good comparsion in details (not well structured though), and gave it as just a reference to Jim with no intension at all.
0
 
Duy PhamFreelance IT ConsultantCommented:
@Jim:  I do understand the EE's concern about blink link, and to be honest I couldn't agree with EE more on that.

At the time I saw the Enrique's question, my first thought was 'Hey, this guy is asking exactly the same question we did jump into when optimizing our queries few years ago. I can just gave him the helpful link we got before.'. That's it.

Anyway, thanks for guiding me. Is anything I should do to keep this thread from being 'dead' in the future in case the link disappear?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.