locked
Partitioning table on CHAR column RRS feed

  • Question

  • Hi All,

    Got a table in SQL 2008 which has records for different states.
    What will be a better way to go for partition for... Example 26 partitions as A, B, C etc.?
    OR
    Creating partitions with names of States such as CA, AZ, OH etc or all individual cases??

    In either of 2 cases, what can be the partition for other exceptional data?

    Please suggest.

    Thanks

    Tuesday, December 21, 2010 5:26 AM

Answers

  • Hi Uri,

    I didn't try this, but should the state codes in the partition
    function not be ordered alphabetically?


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Tom Li - MSFT Wednesday, December 29, 2010 9:01 AM
    Wednesday, December 22, 2010 9:54 AM

All replies

  • Can you show us the structure of the table?

    CREATE PARTITION FUNCTION PF_TargetTable1(varchar(2))
    AS RANGE LEFT FOR VALUES
    (
        'CA',
        'AZ',
        'OH',
        .....

      )
    GO

    CREATE PARTITION SCHEME PS_TargetTable1
    AS PARTITION PF_TargetTable1
    ALL TO ([PRIMARY])
    GO

    CREATE TABLE [dbo].[TargetTable1]
    (
         [State] [varchar](2) NOT NULL,
        [id] [bigint] IDENTITY(1,1) NOT NULL,
        CONSTRAINT [PK_TargetTable1_Staging] PRIMARY KEY CLUSTERED
        (
            State,
            id
        ) ON PS_TargetTable1(State)
    )


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, December 21, 2010 7:43 AM
  • Hi Uri,

    I didn't try this, but should the state codes in the partition
    function not be ordered alphabetically?


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Tom Li - MSFT Wednesday, December 29, 2010 9:01 AM
    Wednesday, December 22, 2010 9:54 AM
  • Hi Hugo

    Yep, desirable :-))) Thanks for pointing that out


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 22, 2010 9:57 AM
  • Hi Hugo

    Yep, desirable :-))) Thanks for pointing that out


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 22, 2010 9:57 AM