Post reply

Temp table limitations

  • May 23, 2014 at 11:20 am

    #1716190

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    This is really awesome, sql wise. Thank you for the link.

    (actually the correct people are wrong and some selected wrong people are actually right)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • May 23, 2014 at 11:25 am

    #1716193

    Raghavendra Mudugal (5/23/2014)

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    This is really awesome, sql wise. Thank you for the link.

    You're welcome.

    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

  • May 23, 2014 at 12:56 pm

    #1716235

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    Wow. I'm surprised. I can't imagine what I would use it for, but it seems eminently reasonable for the feature to be available even tough until now I believed it wasn't.

    Tom

  • May 23, 2014 at 12:57 pm

    #1716237

    TomThomson (5/23/2014)

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    Wow. I'm surprised. I can't imagine what I would use it for, but it seems eminently reasonable for the feature to be available even tough until now I believed it wasn't.

    Yeah it is kinda hard to figure out a possible use-case. But it is kinda cool and fun to play with at least. 😎

    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

  • May 23, 2014 at 2:33 pm

    #1716258

    SQLRNNR (5/23/2014)

    TomThomson (5/23/2014)

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    Wow. I'm surprised. I can't imagine what I would use it for, but it seems eminently reasonable for the feature to be available even tough until now I believed it wasn't.

    Yeah it is kinda hard to figure out a possible use-case. But it is kinda cool and fun to play with at least. 😎

    Very nice post Jason. That script clearly demonstrates partitioning on a temp table. Saved that link to my favorites!:-D

    I've been trying to think of why you would ever need to partition a temp table for the past few minutes....Nope. I have no idea why that would be useful.

  • May 23, 2014 at 2:43 pm

    #1716263

    KWymore (5/23/2014)

    SQLRNNR (5/23/2014)

    TomThomson (5/23/2014)

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    Wow. I'm surprised. I can't imagine what I would use it for, but it seems eminently reasonable for the feature to be available even tough until now I believed it wasn't.

    Yeah it is kinda hard to figure out a possible use-case. But it is kinda cool and fun to play with at least. 😎

    Very nice post Jason. That script clearly demonstrates partitioning on a temp table. Saved that link to my favorites!:-D

    I've been trying to think of why you would ever need to partition a temp table for the past few minutes....Nope. I have no idea why that would be useful.

    Thanks, glad you enjoyed it.

    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

  • May 23, 2014 at 4:27 pm

    #1716274

    Since Jason just proved that you can partition a temporary table, let's have a sub-Q0TD (no QoTD points for this):

    Can you partition a table variable?

    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

  • May 23, 2014 at 4:48 pm

    #1716277

    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/

  • May 23, 2014 at 5:31 pm

    #1716282

    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.

    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/

  • May 23, 2014 at 11:58 pm

    #1716296

    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.

    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

  • May 24, 2014 at 7:54 am

    #1716311

    TomThomson (5/23/2014)

    Nice easy question. I was quite surprised to see that more than twice as many thought constraints were not allowed as thought indexes were not allowed, and also quite surprised that 30% got it wrong although. Maybe people don't much use temp tables?

    might be that & now it`s 33% wrong answers :w00t:

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • May 24, 2014 at 8:06 am

    #1716314

    SQLRNNR (5/23/2014)

    I disagree that this is an easy question. There may be two obviously correct options but there is a third option that is correct.

    Temp tables can be partitioned. With three correct answers and the question requesting a pick 2, it makes it hard to decided which 2 of 3 to pick.

    Here is a rundown of the three options that are correct.

    http://bit.ly/1kb7zz7

    +1 that third option has been removed already from the question when I first read it, obviously after Steve corrected it 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • May 24, 2014 at 8:15 am

    #1716315

    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.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • May 24, 2014 at 9:42 am

    #1716318

    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.

  • May 24, 2014 at 10:18 am

    #1716322

    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.

    Thanks

    I'm not real sure there is a production use case.

    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

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

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