Post reply

The "magical" switch statement.

  • May 24, 2021 at 8:11 pm

    #3887448

    Hey guys

    Thanks in advance for your time and consideration for my question.

    I just learned about the ALTER TABLE src SWITCH TO dst command.  SQL Server 2016 (although I believe it has been around for a while)

    To my thinking, this is a magical way where I could put new indexes on very large tables.

    IE

    ALTER TABLE src SWITCH TO dst

    <create cool new index on SRC>

    ALTER TABLE dst SWITCH TO src

    This feels like finding the fountain of youth.  Since I know that probably does not exist, I would like an experienced eye to tell me either "YES YOU FOUND IT" or "sorry charlie, this wont work for reason xyz"

    Thanks

     

    • This topic was modified 2 years, 11 months ago by  Steve.

    Steve

  • May 24, 2021 at 9:05 pm

    #3887465

    Unfortunately, you missed the requirement that the source and target tables must have identical indexes.

  • May 25, 2021 at 5:47 pm

    #3887852

    That actually has not been my experience, hence my question.

    I did encounter the CLUSTER index requirement, but in practice the regular indexes have not seemed to matter.

    My test was something like

    CREATE TABLE src

    (

    COL1 INT

    COL2INT)

    Cluster Index COL1

    NonCluster index COL2

    CREATE TABLE dst

    (

    COL1 INT

    COL2INT)

    Cluster Index COL1

    NO INDEX COL2

    ALTER TABLE src SWITCH dst

    This seems to work just fine.

    This is why I am asking about the ability to SWITCH the data, create a new INDEX, (not constraint and not unique)

    I guess if no one has tinkered with this I can just run some test and see what floats.  But I was hoping to get some advice on why this would or would not be a good idea.

    Steve

  • May 25, 2021 at 6:17 pm

    #3887855

    That's pretty interesting, Steve.  I'm going to have to give that a try on a much larger table.  I'll be back but not today... I've got a really full dance card.  This could have some pretty good ramifications if it works the way you say it seems to.

    If you run some tests, please absolutely DO share what you're doing.  It might make for a very useful SQL Server "Oolie", indeed!

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 11, 2021 at 1:01 am

    #3894701

    OK, I did some testing and this is not the magic I could have dreamed of.

    While I can SWITCH to a table that does not have matching indexes, I cannot switch back to the original table with "new" indexes.

    I am going to continue tinkering with this command because it seems pretty useful under certain scenarios.  It is just not the sword in the stone I had hoped for.

    Thanks for considering this with me.

    Steve

Viewing 5 posts - 1 through 4 (of 4 total)

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