Table Variable

Question

Table Variable

Viewing 12 posts - 16 through 27 (of 27 total)

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

mtassin SSC-Insane Points: 23099 More actions · Answer 1

Hugo Kornelis (8/16/2012)
sestell1 (8/16/2012)
A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.
Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:
"There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."

But you don't get the IDENTITY reseed that Truncate gives.

DECLARE @bob TABLE(

tid INT IDENTITY(1,1) PRIMARY KEY,

tval VARCHAR(50))

INSERT @bob DEFAULT VALUES

SELECT * FROM @bob

DELETE @bob

INSERT @bob DEFAULT VALUES

SELECT * FROM @bob

That said, I found reading the comments kind of amusing.

"We're using table variables because of the performance increase from temp tables"

Ummm

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

sestell1 SSChampion Points: 10230 More actions · Answer 2

Neat trick mtassin, I've never seen an insert statement using DEFAULT and no columns listed before.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

mtassin (8/16/2012)
Hugo Kornelis (8/16/2012)
sestell1 (8/16/2012)
A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.
Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:
"There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."
But you don't get the IDENTITY reseed that Truncate gives.
DECLARE @bob TABLE(
tid INT IDENTITY(1,1) PRIMARY KEY,
tval VARCHAR(50))
INSERT @bob DEFAULT VALUES
INSERT @bob DEFAULT VALUES
INSERT @bob DEFAULT VALUES
INSERT @bob DEFAULT VALUES
INSERT @bob DEFAULT VALUES
SELECT * FROM @bob
DELETE @bob
INSERT @bob DEFAULT VALUES
SELECT * FROM @bob
That said, I found reading the comments kind of amusing.
"We're using table variables because of the performance increase from temp tables"
Ummm

+1

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

Ken SSCoach Points: 17191 More actions · Answer 4

Nice question. I have never considered truncating a table variable before.

Narud SSCrazy Points: 2837 More actions · Answer 5

Now I have one more reason to love table variables

Kwex Default port Points: 1407 More actions · Answer 6

Kwex

Default port

Points: 1407

August 17, 2012 at 2:50 am

#1526956

Hmmm, learned something new today ... Thanks!

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

I didn't know we could have an identity column in a table variable.

And that it can't be reset. I was expecting it wouldn't be possible to truncate the table though.

I wonder what you guys use the most: temp tables or table variables in terms of performance. I know that temp tables can have additional indexes, etc. but it can also have the effect of locking tempdb while it's being created or altered.

Anyway, that was an excellent question for me.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

EL Jerry SSCertifiable Points: 7063 More actions · Answer 8

Great question. Learned something new, now I have to remember that the next time I use table variables.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 9

codebyo (8/17/2012)
I wonder what you guys use the most: temp tables or table variables in terms of performance.

It depends on the context. Table variables can have significant performance advantages when it comes to creating them very quickly, and temporary tables have some very strange behaviours. Most times, where an execution plan choice depends on the contents of a temporary object, I choose temporary tables, but I am *very careful* about the details. (More information at http://bit.ly/TempTables and http://bit.ly/TempCaching)

I know that temp tables can have additional indexes, etc. but it can also have the effect of locking tempdb while it's being created or altered.

The 'locking tempdb' thing hasn't been an issue since SQL Server 6.5, though the myth has lasted extremely well

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

SQL Kiwi (8/21/2012)
The 'locking tempdb' thing hasn't been an issue since SQL Server 6.5, though the myth has lasted extremely well

I wasn't aware it was a myth. :w00t:

I failed to answer one question related to this at a job interview and there were three experts in that room telling me that that was a potential issue in their production servers and I should have known about tempdb locking and temporary tables. Note: the company is world leader in mobile platform software.

They said I failed the interview because I didn't know that.

Thank you for the correction. I wouldn't know it is a myth if it wasn't for you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Hugo Kornelis (8/16/2012)
sestell1 (8/16/2012)
A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.
Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:
"There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."

Yes, but the MS guy got it wrong, there is a difference. For temporary tables, TRUNCATE TABLE resets the identitity sequence, and delete does not reset it. Even worse, the other method of resetting the identity sequence (DBCC CHECKIDENT) is also invalid for table variables, so for a table variable there's no way of emptying the table and restarting the identity sequence.

Tom

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 12

Thanx learn something TODAY;-)

Neeraj Prasad Sharma
Sql Server Tutorials

Table Variable

Cookies on SQLServerCentral