Table Variable

Question

Table Variable

Viewing 15 posts - 16 through 30 (of 35 total)

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 1

bitbucket-25253 (8/31/2012)
Rather suprised at the number of incorrect answers. Would have thought that the percent correct would be closer to 100

You overestimate how awake some of us are in the AM. 🙂

Actually, I have never even thought of using identity insert for a table variable. In what situations have you had to use it?

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

DugyC (8/31/2012)
IgorMi (8/31/2012)
Nice question!
It is good to know the advantages and disadvantages of temp tables and temp variable tables
The following code will work fine:
create table #tmp(SlNo int identity, Name varchar(200))
INSERT INTO #tmp(Name) Values('SCREW')
INSERT INTO #tmp(Name) Values('HAMMER')
INSERT INTO #tmp(Name) Values('SAW')
DELETE FROM #tmp WHERE SlNo = 2
SET IDENTITY_INSERT #tmp ON
INSERT INTO #tmp(SlNO,Name) Values(2,'SHOVEL')
SELECT * FROM #tmp
Thanks
IgorMi
...giving the following output...
1SCREW
2SHOVEL
3SAW
...whereas if you use a permenant table 'tmp', the output is subtly different...
1SCREW
3SAW
2SHOVEL
Great question, and nice and easy for a Friday. Thanks.

Thank you for your reply

However consider that "The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified"

I run the above code with #tmp and tmp tables, run it on sql server 2005 and 2008 and the same result:

SlNoName

1SCREW

2SHOVEL

3SAW

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

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

sknox (8/31/2012)
bitbucket-25253 (8/30/2012)
Nice question on the basics
...A better supporting document/respected DBA is this blog posting by Steve Jones
http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/
Agreed. Also, if you haven't voted on the Connect submission for this issue, please consider doing so:
https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables

Voted

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]

sestell1 SSChampion Points: 10230 More actions · Answer 4

Nice, I new the answer and managed to click on the correct answer as well.

Today is already shaping up to be better than yesterday. 🙂

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

bitbucket-25253 (8/31/2012)
sknox (8/31/2012)
bitbucket-25253 (8/30/2012)
Nice question on the basics
...A better supporting document/respected DBA is this blog posting by Steve Jones
http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/
Agreed. Also, if you haven't voted on the Connect submission for this issue, please consider doing so:
https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables
Voted

I voted as well - but I voted it down. I fail to see any realistic situation where I would need to be able to use IDENTITY_INSERT on a table variable, so I'd rather see the SQL Server Engineering team spend their time on other features.

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/

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

sknox (8/31/2012)
Agreed. Also, if you haven't voted on the Connect submission for this issue, please consider doing so:
https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables

You got my vote!

This was a great easy question for Friday and leading up to the holiday.

I would have to agree with Thomas as well on the number of incorrect answers; some people are not just morning persons and it's at least once a week where I just zombie through QotD. :hehe:

Everything is awesome!

Error Handler Ten Centuries Points: 1086 More actions · Answer 7

Nice Question to end up the week....

Best,
Naseer Ahmad
SQL Server DBA

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

Hugo Kornelis (8/31/2012)
I voted as well - but I voted it down. I fail to see any realistic situation where I would need to be able to use IDENTITY_INSERT on a table variable, so I'd rather see the SQL Server Engineering team spend their time on other features.

I have to agree... considering what table variables are... if you're going to do enough work on the table that you need to be able to set identity_insert on, you probably also can benefit from the statistics and other things you get with a temp table or permanent one.

--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]

Narud SSCrazy Points: 2837 More actions · Answer 9

Opposite of what happened yesterday, and still proudly, again I'm part of the 77%, but now with the right answer. Some days are better than others 😎

EL Jerry SSCertifiable Points: 7063 More actions · Answer 10

Good, easy question to end the week.

I agree with Hugo that there would be no reason to use IDENTITY in a table variable, but it is up to Microsoft to fix it or not.

Happy weekend everyone, happy long weekend to you guys in the US and Canada!!!

Edit:added Canada, they also have a holiday.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

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

Ken SSCoach Points: 17191 More actions · Answer 11

Good question. I recalled a similar question recently and so I got this right.

I don't know why you would need to do identity_insert on a table variable so I have to agree with Hugo on voting against this fix. There are definitely better items for the SQL Server team to work on.

Happy Friday

WayneS SSC Guru Points: 95460 More actions · Answer 12

I'm curious as to why a SQL 2000 reference was used (although the 2012 reference states the same, this one is specifically for 2000).

There are a bunch of limitations and restrictions of table variables at table (Transact-SQL) - I think that if using IDENTITY_INSERT is prohibited, then it ought to be listed here. Seems like a simple enough thing to make the documentation correct.

I'll vote the connect item up, but for updating the documentation.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

aochss SSCommitted Points: 1677 More actions · Answer 13

Oops, I was in the percent, who answered it wrong. I totally glossed over the table variable and that the second insert said shovel instead of hammer.

It falls into the same area that one skips certain words/letters when skimming an article.

The lesson here is to read the scripts you are debugging/reading very carefully and NOT assume anything.

Thanks for the reminder to be more careful. Great question just for that reminder.

Anton

TomThomson SSC Guru Points: 104773 More actions · Answer 14

bitbucket-25253 (8/30/2012)
Nice question on the basics[rant]
Unfortunately the cited documentation does not support the correct answer.
A better supporting document/respected DBA is this blog posting by Steve Jones
http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/
[/rant]

I agree, this is certainly bad implementation (if the feature is supposed to be not supported, the error message should tell us that and not just produce a meaningles syntax error message; if it is supposed to be supported it should work, not produce an error) and perhaps bad documentation too.

I hope lots of people will follow the link in Steve's blog posting and vote up the connect item.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 15

arthurolcot (8/31/2012)
Nice question, although i think the answers made it easier just through a process of elimination. 🙂

yes, nice question but terrible selection of answer options. If the option 1 Saw, 3 Screw, 2 Shovel had been included it would have been much easier for people to get it wrong.

Tom