Which is Best to use, #Temp Table or @Table Variable?

Question

Post reply

Which is Best to use, #Temp Table or @Table Variable?

Viewing 4 posts - 31 through 33 (of 33 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Gary Harding (8/14/2015)

GilaMonster (8/13/2015)

Eric M Russell (8/13/2015)

Also it supports statistics and non-clustered indexes...

Table variables can have nonclustered indexes. No stats, but they can have nonclustered indexes. You just have to define them in the initial table definition.

Table variables can have nonclustered indexes, but I thought they had to be tied to a PRIMARY KEY or UNIQUE constraint. Can you define a nonclustered index on a non-unique set of columns in a table variable?

Yes as of 2014. You can define the index inline in the table declaration.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Gary Harding (8/14/2015)

Can you define a nonclustered index on a non-unique set of columns in a table variable?

No, at least not before SQL 2014, but you can always add an INT IDENTITY column and make it the last column of a unique constraint, if you don't have a primary key in the table already.

NC indexes on temp structures aren't, in my experience, all that useful. The option's there though, if needed.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

GilaMonster (8/14/2015)

Gary Harding (8/14/2015)

Can you define a nonclustered index on a non-unique set of columns in a table variable?

No, at least not before SQL 2014, but you can always add an INT IDENTITY column and make it the last column of a unique constraint, if you don't have a primary key in the table already.

NC indexes on temp structures aren't, in my experience, all that useful. The option's there though, if needed.

I agree. Normally temp structures contain only the data that would be used and, most of the time, the data will all be consumed so no need for indexes. If you have to "step" through the rows to support emailing or some such, then an index on the counter column (usually and IDENTITY column) could help performance a bit.

--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)
Intro to Tally Tables and Functions

pdanes SSCrazy Eights Points: 8441 More actions · Answer 4

Jeff Moden (8/14/2015)

pdanes (8/14/2015)

If you are manually running some code in SSMS, table variables have the additional advantage of being generated fresh on every run. Temp tables stay around, and you have to add code to either clear their contents, or delete and re-create them on every non-first run.

That can also be a disadvantage if you're troubleshooting and need to see the content or it needs to persist so you can troubleshoot a section after the "table" creation without having to rerun all the preceding code.

Certainly. If you suspect that you may need the contents for anything more complex, or if you need to examine the contents manually after the code finishes, temp tables are clearly a better choice. I use table variables in places where a simple SELECT * FROM @tbl is sufficient to show me the contents on the fly, because those contents are small and easily generated.