That's a Switch!

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

  • 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”

  • Very good question, thank you indeed!


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

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


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

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

  • Good question. Thanks.

    Kingston Dhasian

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

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

  • 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

  • I knew all tables have at least 1 partition, but I didn't think it should be possible to switch to partition 5 as it doesn't exists.

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

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