Table Variable

Question

Post reply

Table Variable

MissTippsInOz

Hall of Fame

Points: 3160
More actions
August 15, 2012 at 8:44 pm

#265099

Comments posted to this topic are about the item Table Variable
August 16, 2012 at 2:14 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 27 total)

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Good one. Learnt something obvious 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Keld Laursen (SEGES) SSC Eights! Points: 843 More actions · Answer 3

It is also worth noting that you have no method of resetting the identity counter for the table.

Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

Keld Laursen (VFL) (8/15/2012)
It is also worth noting that you have no method of resetting the identity counter for the table.
Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂

Very rightly pointed out Keld. Truncate resets the identity counter for the table..

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Keld Laursen (SEGES) SSC Eights! Points: 843 More actions · Answer 5

Lokesh Vij (8/15/2012)
Keld Laursen (VFL) (8/15/2012)
It is also worth noting that you have no method of resetting the identity counter for the table.
Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂
Very rightly pointed out Keld. Truncate resets the identity counter for the table..

Which were my point exactly: No truncate, no reset. Other DML to reset the counter will also fail.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 6

Keld Laursen (VFL) (8/15/2012)
Lokesh Vij (8/15/2012)
Keld Laursen (VFL) (8/15/2012)
It is also worth noting that you have no method of resetting the identity counter for the table.
Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂
Very rightly pointed out Keld. Truncate resets the identity counter for the table..
Which were my point exactly: No truncate, no reset. Other DML to reset the counter will also fail.

I was just trying to re-iterate, as Table variable does not allow truncate to be used, identity counters will not reset 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Stewart "Arturius" Campbell (8/16/2012)
Good question, thanks.
Learned something here.

+1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Nice question. Thanks for the contribution. Never thought of truncating a temp table. But, it reminded me about limitations of this feature.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 9

(Bob Brown)

SSCrazy

Points: 2705

August 16, 2012 at 5:47 am

#1526500

Great learning question for me.

sestell1 SSChampion Points: 10230 More actions · Answer 10

A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

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."

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sknox SSChampion Points: 12388 More actions · Answer 12

Had to double-check the use of IDENTITY in table variables.

Good question -- and good answer choices!

Dana Medley SSCertifiable Points: 6764 More actions · Answer 13

Thomas Abraham (8/16/2012)
Nice question. Thanks for the contribution. Never thought of truncating a temp table. But, it reminded me about limitations of this feature.

+ 1

Everything is awesome!