That's a Switch!

  • sknox


    Points: 12294

    Comments posted to this topic are about the item That's a Switch!

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71915

    Nice question, thanks Mr Knox.
    Have built numerous such operations on partitioned tables, never thought to use it on non-partitioned tables.

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • HappyGeek


    Points: 18684

    Very good question, thank you indeed!


  • paul s-306273


    Points: 10615

    Two thirds right, but assumed the data wouldn't get switched.

  • TomThomson

    SSC Guru

    Points: 104773

    Definitely an interesting question.  But the references in the explanation provide not a clue about the answer, as far as I can tell.


  • George Vobr

    SSCrazy Eights

    Points: 9250

    Fortunately, I found in the documentation an example with ALTER TABLE ... SWITCH into non-partitioned table ... 😉
    Thanks Sknox for an interesting question and also for the very good comprehensive explanation.

  • sknox


    Points: 12294

    paul s-306273 - Thursday, June 29, 2017 3:38 AM

    Two thirds right, but assumed the data wouldn't get switched.

    This question came about because I was scripting a bunch of data moves for partitioned tables, and a pair of non-partitioned tables slipped into the script. I noticed right after hitting "Execute" (isn't that always the case?)

    Since you don't get a row count with a partition switch, all I saw were the warnings, and I had to check whether the data moved or not.

  • Kingston Dhasian


    Points: 19794

    Good question. Thanks.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden

  • RohitK25

    SSC Enthusiast

    Points: 198

    Never thought of that. This question cleared my doubts.
    Good One!


    SSC Guru

    Points: 281252

    This is a really fun find. I had never considered switching a non-partitioned table. There are some interesting uses for switch with non-partitioned tables.

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

Viewing 10 posts - 1 through 10 (of 10 total)

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