Post reply

SQL 2012 Partitioning Large Table on Nullable Date

  • May 15, 2014 at 12:47 pm

    #291479

    I have a very large table that I need to partition. Ideally the table will write to three filegroups. I have defined the Partition function and scheme as follows.

    CREATE PARTITION FUNCTION vm_Visits_PM(datetime)

    AS RANGE RIGHT FOR VALUES ('2012-07-01', '2013-06-01')

    CREATE PARTITION SCHEME vm_Visits_PS

    AS PARTITION vm_Visits_PM TO (vm_Visits_Data_Archive2, vm_Visits_Data_Archive, vm_Visits_Data)

    This should create three partitions of the vm_Visits table. I am having a few issues, the first has to do with adding a new clustered index Primary Key to the existing table. The main issue here is that the closed column is nullable (It is a datetime by the way). So running the following makes SQL Server upset:

    ALTER TABLE dbo.vm_Visits

    ADD CONSTRAINT [PK_vm_Visits] PRIMARY KEY CLUSTERED

    (

    VisitID ASC,

    Closed

    )

    ON [vm_Visits_PS](Closed)

    I need to define a primary key on the VisitId column, but I need to include the Closed column in order to partition on it.

    Also, I don't quite understand how I would move data between partitions on a monthly basis. Would I simply update the Partition function, or have to to some sort of merge, split, or switch function?

    Thanks in advance!

  • May 15, 2014 at 1:02 pm

    #1714003

    1) You cannot have a PK on a NULLable column. Fix the column attributes or do something different.

    2) Partitioning is a VERY COMPLEX subsystem! You are asking questions that you really should not be asking if you are responsible for proper operations of your database. Mess things up and you can have some VERY big problems. You have a very low probability of success if you read a few blog posts and do a few forum posts and try to put partitioning on a production system. Please get some help to properly design, test, implement and set up maintenance!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 15, 2014 at 1:09 pm

    #1714009

    Thanks for the info. I will probably end up just creating a separate table on another filegroup and move data with an SSIS package to accomplish. I was just tinkering around with the built in partitioning feature on a development environment.

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

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