August 16, 2012 at 7:20 am
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."
You do if you are re-using it for temporary storage for a loop, and are using the identity as your loop sequence. Delete does not reset the identity column. You'd need to either set your loop to get the min/max values, or set your own sequence values without using an identity.
August 16, 2012 at 7:31 am
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-2 TABLE(
tid INT IDENTITY(1,1) PRIMARY KEY,
tval VARCHAR(50))
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
SELECT * FROM @bob-2
DELETE @bob-2
INSERT @bob-2 DEFAULT VALUES
SELECT * FROM @bob-2
That said, I found reading the comments kind of amusing.
"We're using table variables because of the performance increase from temp tables"
Ummm
August 16, 2012 at 7:40 am
Neat trick mtassin, I've never seen an insert statement using DEFAULT and no columns listed before.
August 16, 2012 at 9:10 am
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-2 TABLE(
tid INT IDENTITY(1,1) PRIMARY KEY,
tval VARCHAR(50))
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
INSERT @bob-2 DEFAULT VALUES
SELECT * FROM @bob-2
DELETE @bob-2
INSERT @bob-2 DEFAULT VALUES
SELECT * FROM @bob-2
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
August 16, 2012 at 9:53 am
Nice question. I have never considered truncating a table variable before.
August 16, 2012 at 12:44 pm
Now I have one more reason to love table variables 😉
August 17, 2012 at 2:50 am
Hmmm, learned something new today ... Thanks!
August 17, 2012 at 7:18 am
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
August 17, 2012 at 7:31 am
August 21, 2012 at 12:47 am
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 🙂
August 22, 2012 at 5:04 am
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
September 1, 2012 at 6:51 pm
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
May 30, 2013 at 2:41 am
Thanx learn something TODAY;-)
Neeraj Prasad Sharma
Sql Server Tutorials
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply