Temp table limitations

  • Steve Jones - SSC Editor (5/24/2014)


    Hany Helmy (5/24/2014)


    SQLRNNR (5/23/2014)


    Steve Jones - SSC Editor (5/23/2014)


    Question changed and points awarded back as you can apparently partition a temp table: http://www.sqlservercentral.com/blogs/sqlrnnr/2014/05/23/can-you-partition-a-temporary-table/

    Thanks Steve.

    And by the way - thanks for continuing this feature and providing questions for us.

    And thank u Jason for correcting the question, also for the useful link, although I never experienced (or even heard about) a production case for partitioning temp tables.

    Not sure there is one. I suspect this is a bug, and that the design was that this wouldn't be allowed.

    That's probably true. When thinking about it, you would have to recreate the partition function and scheme after each service restart or at least so it seems.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi Steve,

    curious. I've selected "no FK" as one and only option but it gave me "failed" 🙂

    However - local temptable are valid in the sessein and they can have indexes and constraints as well.

    What was my "mistake" when I have selected "no fk" as single option?

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Uwe Ricken (5/25/2014)


    Hi Steve,

    curious. I've selected "no FK" as one and only option but it gave me "failed" 🙂

    However - local temptable are valid in the sessein and they can have indexes and constraints as well.

    What was my "mistake" when I have selected "no fk" as single option?

    Hi Uwe,

    Your mistake was twofold:

    1. You overlooked the "select 2" in the question

    2. You didn't realize that Steve wrote "local" but actually meant to wrote "global".


    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/

  • Hallo Hugo,

    aaargh - thank you for pointing to that failure.

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • I got it wrong, but the "correct" answers stumped me. How are "local" temporary tables visible across sessions?

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • Nakul Vachhrajani (5/25/2014)


    I got it wrong, but the "correct" answers stumped me. How are "local" temporary tables visible across sessions?

    They are not. Take a look at the notes posted by Hugo.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (5/24/2014)


    When thinking about it, you would have to recreate the partition function and scheme after each service restart or at least so it seems.

    Or create them in Model.

    Can't think of a valid reason to do this in production either.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I don't think the mistake in the corrected answers is that Local should be Global because we are looking for limitations as the correct answers. Probably the word not should have been used in the Local answer.

    "Local temp tables are not visible to all sessions"

  • thanks steve for the question.

  • Hugo Kornelis (5/23/2014)


    I was surprised to see so many people claim that this is an easy question, since there is a very glaring error - there is only one error.

    But after reading the discussion I understand that the question was changed. Not for the better, though.

    Local temporary tables are not visible to all sessions, only to the current session. Global temporary tables are visible to all sessions.

    I'm glad I'm not the only one who found the question was difficult since I could only find 1 correct answer and there was supposed to be 2.

  • Hugo Kornelis (5/25/2014)


    Uwe Ricken (5/25/2014)


    Hi Steve,

    curious. I've selected "no FK" as one and only option but it gave me "failed" 🙂

    However - local temptable are valid in the sessein and they can have indexes and constraints as well.

    What was my "mistake" when I have selected "no fk" as single option?

    Hi Uwe,

    Your mistake was twofold:

    1. You overlooked the "select 2" in the question

    2. You didn't realize that Steve wrote "local" but actually meant to wrote "global".

    good catch. I was thinking which things weren't valid instead of the other way around.

Viewing 11 posts - 31 through 40 (of 40 total)

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