Post reply

SQL Partition

  • September 15, 2011 at 10:04 pm

    #257388

    Comments posted to this topic are about the item SQL Partition

    Always Think Positive

  • September 15, 2011 at 11:15 pm

    #1382380

    Nice question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 15, 2011 at 11:29 pm

    #1382387

    good question

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • September 16, 2011 at 12:13 am

    #1382392

    This quite interesting topic for DW area. Also when the Partition 1 and 4 requires switching? It should fail as partition values are different.

  • September 16, 2011 at 1:05 am

    This was removed by the editor as SPAM

  • September 16, 2011 at 1:13 am

    #1382399

    Good question, but the answer is totally wrong. The ALTER TABLE statement fails at least for two reasons (and both of these reasons are available at the link given in the explanation):

    1)

    General Requirements for Switching Partitions

    Both the source and the target of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup.

    Thus, after running the ALTER TABLE statement we'll see the following error message:

    ALTER TABLE SWITCH statement failed. Partition 1 of table 'test_db.dbo.trx' is in filegroup 'fg1' and partition 4 of table 'test_db.dbo.trx_hy' is in filegroup 'fg4'.

    2) Let's assume that both partitions reside in the same filegroup.

    Constraint Requirements

    If you are switching a partition of a partitioned table to another partitioned table, the boundary values of the source partition must fit within the boundary values of the target partition.

    Let's create the tables on the following partition scheme:

    create partition scheme ps1

    as

    partition pf1 to ([primary], [primary], [primary], [primary]);

    After running the ALTER TABLE statement we'll see the following error message:

    ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'test_db.dbo.trx' is not a subset of range defined by partition 4 in table 'test_db.dbo.trx_hy'.

    So, the correct answer should be "The statement always fails" 🙂

  • September 16, 2011 at 2:26 am

    #1382419

    Nice question! A reminder on PARTITIONS 🙂

  • September 16, 2011 at 3:27 am

    #1382438

    Good question and thanks for your findings vk-kirov

    M&M

  • September 16, 2011 at 6:11 am

    #1382498

    Very nice question Srikant 🙂

  • September 16, 2011 at 6:27 am

    #1382503

    I learnt something new today - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • September 16, 2011 at 7:33 am

    #1382535

    This is a good topic for the question. I think some tweaks to the question as pointed out by vk-kirov could make it a better question.

    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

  • September 16, 2011 at 10:03 am

    #1382654

    Thanks for the question. I've never done partition switching before so I learned something new.

  • September 16, 2011 at 11:11 am

    #1382686

    A good question. I'm not at all sure about the answer though. After answering with my best guess (and getting a probably undeserved point because I picked what I thought was an obvious fail case) I read the referenced article and realised that I knew even less about partitioning than I had thought.

    Tom

  • September 16, 2011 at 12:34 pm

    #1382722

    good question, but i agree with vk-kirov and SQLRNNR!!!!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • September 17, 2011 at 1:25 pm

    #1382968

    I'd love to see an article showing a real world application of switching partitions.

    I understand it would be great for historical data that can be archived by a date field, or year but I've never made any experiences with it before.

    Great question. Thank you.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

Viewing 15 posts - 1 through 15 (of 23 total)

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