Table Variable

  • Sreepathi1987

    Default port

    Points: 1425

    Comments posted to this topic are about the item Table Variable

  • Ron McCullough

    SSC Guru

    Points: 63877

    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]

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks!

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

  • Arthur Olcot

    SSCertifiable

    Points: 6008

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

  • sknox

    SSChampion

    Points: 12284

    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

  • Erav

    SSC-Addicted

    Points: 411

    Easy One!! Thanks:-)

    -Erav

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    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.

  • archie flockhart

    SSCrazy

    Points: 2339

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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • DugyC

    Hall of Fame

    Points: 3804

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

    1 SCREW

    2 SHOVEL

    3 SAW

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

    1 SCREW

    3 SAW

    2 SHOVEL

    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]

  • Ron McCullough

    SSC Guru

    Points: 63877

    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]

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    DugyC (8/31/2012)


    ...giving the following output...

    1 SCREW

    2 SHOVEL

    3 SAW

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

    1 SCREW

    3 SAW

    2 SHOVEL

    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/

  • Thomas Abraham

    SSChampion

    Points: 10761

    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 36 total)

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