Post reply

Viewing Partition tables data

  • March 14, 2014 at 11:21 am

    #396423

    Hi,

    I have created 2 partition tables and can view my partitions by executing this:-

    SELECT * FROM sys.partitions AS p

    JOIN sys.tables AS t

    ON p.object_id = t.object_id

    WHERE p.partition_id IS NOT NULL

    AND t.name = 'TestTable1';

    But how can I view the actual data that is held within each partition? So the actual records in each of my partitions?

    For example if I wanted to edit a row in one of my partitioned tables using UPDATE statement or just view my data using SELECT statement how can I do this?

    Also if I decided to add more data into an existing partition how would I do this?

    Thanks.

  • March 14, 2014 at 12:07 pm

    #1697240

    The easiest way to do this is to use a where clause based on your partition function. Say you used a date based function say on month you can use that in your query. select * from table where PartitionedDateColumn = 3 --MarchYou will get partition elimination and it will only return data from the necessary partition(s) instead of using the entire table. Does that help?


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 14, 2014 at 1:03 pm

    #1697272

    To be honest, you shouldn't need to even know the table is partitioned if it's done correctly. The partitioning column, function, and schema should do it all auto-magically.

    --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)

  • March 17, 2014 at 5:23 am

    #1697590

    That's great, thanks for your replies.

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

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