CODEWITHSUNDEEP
sql-server-2008partitioning
Robin clave
Robin clave

Reputation: 638

How to do partition in sql server 2008

I am having transaction table in sql server 2008 db. I would like to do daily partition on this table. So i used the partition creation wizard. I gave the partition schema, patition function everything. I set the boundary values as 1.1.2011 and 10.1.2011 and daily level. then i entered the values to that table as each day one row (just for checking ), it will get insert into each partition. Atlast each partition contain 1 row each. But i want in such a way, if i entered on 11.1.2011, it will be loaded to 1st partition be deleting the existing data.. How i can do this.

Upvotes: 0

Views: 1118

Answers (1)

Andrew
Andrew

Reputation: 27294

From a technical standpoint this is possible, and you are trying to duplicate a form of interval partitioning that Oracle has, where the new partitions are generated on the fly for you. In SQL Server you have to manage this process entirely yourself - the partition maintenance, creation / deletion of partitions must be coded within procedures.

So can it be done? yes - you can simulate interval partitioning by adding a trigger, and I knocked up an example of this a while back (http://sqlfascination.com/2010/09/12/interval-partitioning-in-sql-server-2008/ )

Should you do it? I severely doubt it - the performance hit will not be insignificant, and managing the partitions in a trigger was an exercise in testing, not production code.

If you know that each night you need to move the partition forward, you can schedule a stored proc to handle this all for you, which would be far more efficient than using a trigger.

Upvotes: 1

Related Questions