Partition Table by Varchar column

  • I have a table that I want to partition by postcode (example values are: 7420_FMIL, ND8_1ZE, C7_4JM) where each rows for a certain postcode would get inserted into its own respective filegroup (Rows for Postcode 7420_FMIL would be in Filegroup FG_7420_FMIL for example) .

    What would the partition function look like?

    CREATE PARTITION FUNCTION PF_Postcode(varchar(100))

    AS RANGE LEFT FOR VALUES

    (

    7420_FMIL,

    ND8_1ZE,

    C7_4JM

    )

    Would that work?  Any help would be much appreciated

  • Before going down the path of partitioning - what are you expecting to achieve by doing so?  What is the purpose of partitioning - and more to the point, why are you wanting to separate the data into separate filegroups and files?

    You don't have to use separate filegroups for partitioning - and there are some benefits to having all partitions in the same filegroup.  If you setup multiple filegroups where the files are all on the same drive then there really isn't any benefit to multiple filegroups.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I think it would be as below.  You must put the partition values in alpha order.

    CREATE PARTITION FUNCTION PF_Postcode(varchar(100))

    AS RANGE LEFT FOR VALUES

    (

    '7420_FMIL',

    'C7_4JM',

    'ND8_1ZE'

    )

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • This was removed by the editor as SPAM

  • Millicentsa wrote:

    Much appreciated so a phenomenal plan, your thought worked for me.

    I smell spam cooking.

    --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)
    Intro to Tally Tables and Functions

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

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