CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
mehdi lotfi
mehdi lotfi

Reputation: 11581

Alter partition function and partition schema automatically

The structure of my tables are below :

SalesCompanyFinancialPeriod (ID int, ...)
Document (ID int, SalesCompanyFinancialPeriodID Int, ...)
DocumentDetail (ID Int, DocumentID Int, ...)

I want to create a partition function and partition schema for partitioning the Document table and DocumentDetail table, using SalesCompanyFinancialPeriodID column value. I also want to automatically alter this partition schema and partition function using an after trigger on SalesCompanyFinancialPeriod table.

In other word, I want to automatically create a filegroup in my database when a new salescompanyfinancialperiod record is created, and partition the records of Document table and DocumentDetail table with a new salescompanyfinancialperiodid in this newly created filegroup.

How can I do this?

Upvotes: 0

Views: 1419

Answers (1)

David Manheim
David Manheim

Reputation: 2626

See http://sqlfascination.com/2010/09/12/interval-partitioning-in-sql-server-2008/, which does almost exactly this (Based on 1 table, but it is the same idea.)

He notes that according to MS, the DML trigger cannot do this directly; quoting Books OnLine: "...the following Transact-SQL statements are not allowed inside the body of a DML trigger when it is used against the table or view that is the target of the triggering action ..., ALTER PARTITION FUNCTION, ..."

He says it is untrue, but I would be careful. You could, instead, create a stored procedure that altered the partitions that is run based on a trigger. This is somewhat more safe, as the statement would need to run as the database owner and have dataspace permissions, which might be scary to have in a trigger directly.

Side note - In SQL 2008, there is no list partition, only range partitions, so this would be annoying even manually. You can trick it, per the following: http://www.sqlservercentral.com/articles/partition/64740/

Upvotes: 1

Related Questions