Partitioned Tables in SQL Server 2005

  • is thisposible to Partitioned the existing Tables in SQL Server 2005

    if yes tell me how with demo...

  • varun-jha (10/24/2007)


    is thisposible to Partitioned the existing Tables in SQL Server 2005

    if yes tell me how with demo...

    Similarly to changing filegroups you cannot repartition existing tables with a single statement. What you can do is to rebuild the table.

    The easiest way to get a good enough script is to use Management Studio. Click on table, edit, then in the properties change the partitioning (set dataspace type, partition scheme and the partition column).

    Then you can click in the top left corner the "Save Change Script"

    An example script that tells you more or less what to do is:

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_toBePartitioned1

    (

    a int NULL,

    b int NULL

    ) ON ps1(a)

    GO

    IF EXISTS(SELECT * FROM dbo.toBePartitioned1)

    EXEC('INSERT INTO dbo.Tmp_toBePartitioned1 (a, b)

    SELECT a, b FROM dbo.toBePartitioned1 WITH (HOLDLOCK TABLOCKX)')

    GO

    DROP TABLE dbo.toBePartitioned1

    GO

    EXECUTE sp_rename N'dbo.Tmp_toBePartitioned1', N'toBePartitioned1', 'OBJECT'

    GO

    COMMIT

    Note that Management Studio does not always get this right, so it is important that you check what it generates. You could use this as a skeleton. Alternatively you could use third party tools too.

    As always, please make a backup before moving to partitioning 🙂

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • thanks for reply andras..

    it will clear my concepts

    thanks.

  • You have a couple options to take an existing non-partitioned table and convert it to an partitioned table.

    Hint: see BOL, Look for: "partitioned tables [SQL Server], modifying" in the index. In the topic, scroll down to "Converting a Nonpartitioned Table to a Partitioned Table"

    Option #1

    Similar to changing filegroups, you can simply recreate the clustered index on the partition function, including the DROP_EXISTING clause.

    Option #2

    You can also perform a partition switch (ALTER TABLE...SWITCH), where SQL Server will switch the metadata about where the data in a table is stored between two tables. The basic concept:

    * Create a new empty partitioned table with only a single partition defined using the same DDL (plus the partition clause) as the existing unpartitioned table.

    * Using ALTER TABLE...SWITCH, SQL Server will switch the data between the two tables by simply updating the metadata - the data from the original unpartitioned table will suddenly be 'owned' by the new partitioned table, and the data from the partitioned table (of which there is none) becomes 'owned' by the unpartitioned table. Drop/rename the original, then rename the new, and you're done. No copying necessary. (There are restrictions, of course - check the BOL topics and try it out a few times in a test environment)

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • try out this link and see to that if this works for you...

    http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

  • Great responses. Also, planning is KEY! I have a script that illustrates some of the partitioning limitations in 2005 at http://www.ricksql.com. I use it in my presentations when discussing this concept.

  • Hello,

    as I can see there are the understandalbe question and answers but can anyone explain to me about this part of SQL code

    ...

    ) on ps1(a)

    ...

    (it is a Partition Schema)

    so where the code for this schema! - little bit confused!!!???

    thnx!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Dugi (11/18/2007)


    Hello,

    as I can see there are the understandalbe question and answers but can anyone explain to me about this part of SQL code

    ...

    ) on ps1(a)

    ...

    (it is a Partition Schema)

    so where the code for this schema! - little bit confused!!!???

    thnx!

    The included code sample is what Management Studio generates for rebuilding a particular table. In order to select the new partition scheme in Management Studio (or to use it in case you do the above process manually), you need to create the partition function (http://technet.microsoft.com/en-us/library/ms187802.aspx) and partition scheme (http://msdn2.microsoft.com/en-us/library/ms179854.aspx) (and the filegroups) yourself. This you can do by using statements like:

    CREATE PARTITION FUNCTION pf1 (int)

    AS RANGE RIGHT FOR VALUES (1000,2000,3000);

    CREATE PARTITION SCHEME ps1

    AS PARTITION pf1

    ALL TO ( [PRIMARY] );

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras thnx for info and links!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

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