Post reply

Table Variable

  • August 30, 2012 at 9:30 pm

    #266054

    Comments posted to this topic are about the item Table Variable

  • August 30, 2012 at 9:48 pm

    #1532196

    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]

    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]

  • August 30, 2012 at 9:50 pm

    #1532197

    An easy one to end this week. Thanks 🙂

    ~ 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

  • August 31, 2012 at 12:03 am

    #1532221

    Easy one, thanks!

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

  • August 31, 2012 at 1:20 am

    #1532238

    Nice question, although i think the answers made it easier just through a process of elimination. 🙂

  • August 31, 2012 at 1:51 am

    #1532244

    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

  • August 31, 2012 at 2:28 am

    #1532253

    Easy One!! Thanks:-)

    -Erav

  • August 31, 2012 at 2:54 am

    #1532258

    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/

    It may just be an astounding coincidence, but the layout of Steve's example and the one in the Question appear to be identical - only the names have changed.

    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.

  • August 31, 2012 at 2:57 am

    #1532259

    The hardware-related nature of this question reminded me of the urban legend about the newspaper story about the inmate who escaped from the asylum, raped one of the cleaners, and fled. The headline in the paper was "Nut screws washer and bolts".

  • August 31, 2012 at 3:22 am

    #1532266

    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

    Igor Micev,My blog: www.igormicev.com

  • August 31, 2012 at 5:11 am

    #1532307

    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.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • August 31, 2012 at 5:16 am

    #1532309

    Rather suprised at the number of incorrect answers. Would have thought that the percent correct would be closer to 100 percent.

    At 7:15 AM

    Correct answers: 79% (283)

    Incorrect answers: 21% (74)

    Total attempts: 357

    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]

  • August 31, 2012 at 5:29 am

    #1532313

    Great to see than 79% got it right.

    I remember this being discussed lately in a different topic.

    I love questions that expose limitations/features of a given functionality.

    It helps me choose the right tool for the right job.

    Thank you.

    Best regards,

    Andre Guerreiro Neto

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

  • August 31, 2012 at 5:34 am

    #1532314

    DugyC (8/31/2012)

    ...giving the following output...

    1SCREW

    2SHOVEL

    3SAW

    ...whereas if you use a permenant table 'tmp', the output is subtly different...

    1SCREW

    3SAW

    2SHOVEL

    Striclty speaking, since there is no ORDER BY on the SELECT statement, these two outputs are exactly the same. A SELECT without ORDER BY specifies that any order in which rows are returned is valid; that order may even change between one execution and the next execution of the same query.

    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/

  • August 31, 2012 at 5:39 am

    #1532315

    Andrew Watson-478275 (8/31/2012)

    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/

    It may just be an astounding coincidence, but the layout of Steve's example and the one in the Question appear to be identical - only the names have changed.

    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.

    Agreed. In fact, I ended up going for the first answer, convincing myself that it was just a mistake, and that the OP had intended shovel to be hammer. Problems with some earlier questions have me not even taking simple questions at face value. Good thing its a three day weekend for some of us. 🙂

    Thanks for the question!

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

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

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