Post reply

Partition Function

  • February 25, 2008 at 1:01 pm

    #183280

    Guys,

    I have names in the database which I want partition by last name - for example last names starting with A, B, C, D should go to the file group 1. last names starting with E, F, G, H should go to file group 2.

    I am trying to use the following function - but do I specify in the function that last names with with A, B, C, D should go to the file group 1

    CREATE PARTITION FUNCTION myRangePF3 (char(20))

    AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

    Is there any way to modify partition function to accomplish this?

    Any suggestions and inputs would help

    Thanks

  • February 25, 2008 at 2:10 pm

    #781772

    CREATE PARTITION FUNCTION pfNameRange(char(20))

    AS RANGE RIGHT FOR VALUES ('E', 'I', 'N','T');

    would divide the data into 5 partitions:

    1: < 'E'

    2: >= 'E' and < 'I'

    3: >= 'I' and < 'N'

    4: >= 'N' and < 'T'

    5: >= 'T'

    then assign each partition to a filegroup:

    CREATE PARTITION SCHEME PartitionByName

    AS PARTITION pfNameRange

    TO ( filegroup1, filegroup2, ... filegroup5)

    this help page ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/5b21c53a-b4f4-4988-89a2-801f512126e4.htm has good examples.

  • February 26, 2008 at 5:01 am

    #781921

    From your mail I understand you already have the table with data. That adds complexity to the work you have.

    If you want the starting character of the last name to be the factor of the partition, you need to create an additional (computed) column on the first character of Last name

    Alter Table Add LastNameStart As LEFT(LastName,1)

    Now You have to Make this as your Clustered Index. If you already have a clustered Index make that non clustered and create a new clustered index on LastnameStart

    (Note: If you currently have your Primary key or a unique key as clustered, it can remain as Primary/unique key, but not as clustered.)

    CREATE CLUSTERED INDEX CIX_TableName on (LastNamestart)

    Now you need to create a partition function and then partition schema. As you already have data you need to create a partition, whcih can hold all your data. Let me say you have Last, Last name 'Xavior' Then you create a Function that can hold Upto (including xavior)

    The current partition function you have will not work

    CREATE PARTITION FUNCTION myRangePF3 (char(1))

    AS RANGE RIGHT FOR VALUES ('Y');

    CREATE PARTITION SCHEME MyScheme

    AS PARTITION myRangePF3

    TO (' ')

    You need to specify at least one more filegroups than the number of ranges specified inthe function. You can repeat the same file groups.

    Now you need to create ANOTHER table of the same structure, in this partition scheme

    CREATE TABLE dbo.

    (

    -- including the coluted column

    ) ON myScheme(LastNameStart)

    Now the last step: You need to move the data from your old table to the new table. SQL Server provides, SWITCH function for this purpose. As at currently, you have all the data in one aprtition you need to add a constraint to your orginal table to prevent further data

    ALTER TABLE dbo. CHECK (LastNameStart<='Y')

    ALTER TABLE dbo.

    Now you need to add your partitions into the partition function and scheme

    ALTER PARTITION FUNCTION myRangePF3()

    SPLIT RANGE ('E')

    ALTER PARTITION FUNCTION myRangePF3()

    SPLIT RANGE ('I')

    ALTER PARTITION FUNCTION myRangePF3()

    SPLIT RANGE ('S')

    After all drop the old table, and rename the new table.

    Please refer BOL for more details. It has examples too.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • February 26, 2008 at 5:06 am

    #781924

    I believe some of the tags I used within the code doesn't work with the editor. Please add the Tablename in those places..

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

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

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