Table Variable

Question

Table Variable

Viewing 6 posts - 31 through 35 (of 35 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Hugo Kornelis (8/31/2012)
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.

I think that's a bad mistake. You are encouraging MS to give us rotten buggy documentation and unhelpful meaningless error messages. If of course you like bad documentation and sloppy error messages, you've done the right thing, but I didn't think you had those tastes.

Tom

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

See next post

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]

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

L' Eomot Inversé (9/2/2012)
Hugo Kornelis (8/31/2012)
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.
I think that's a bad mistake. You are encouraging MS to give us rotten buggy documentation and unhelpful meaningless error messages. If of course you like bad documentation and sloppy error messages, you've done the right thing, but I didn't think you had those tastes.

Now the effort to do it correctly would be WHAT

Link to BOL SET IDENTITY_INSERT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188059(v=sql.90).aspx

database_name
Is the name of the database in which the specified table resides.
schema_name
Is the name of the schema to which the table belongs.
table
Is the name of a table with an identity column.
-- suggested editing
Use with a table variable will result in an error message
-- end suggested editing

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]

marlon.seton SSCrazy Points: 2623 More actions · Answer 4

Andrew Watson-478275 (8/31/2012)
Another point - the question would have required much more thought if one of the answers had been "1 SCREW, 2 SHOVEL, 3 SAW", which would have been the correct answer if it wasn't for the problem with Identity Insert on table variables.

ORDER BY was not specified so the actually order of the three items in the two wrong answers is surely irrelevant, no?

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 5

It's not the order that I was questioning, but the combination of string and identity. If it wasn't for the "feature" being highlighted, "Shovel" would be in there with an ID of 2, but this wasn't one of the given answers.

udayroy15 Ten Centuries Points: 1076 More actions · Answer 6

nice question...........

it is table variable........not temp or static table