Table Partitioning in SQL 2008

  • Article

  • At the moment I'm working for a client that wants to partition some tables. They need to do that because the tables have grown to immense proportions. One table has 800.000.000 rows. Obviously a lot of inserts are happening on this tables and they hope to improve the performance of these inserts by partitioning the table. At the moment there production servers are running SQL 2005 but we are going to try it on SQL2008 as well. SQL 2008 has some cool gui functions for partitions with sliding windows which you have to program yourself otherwise. Let's have a look:

  •  

  • When you right-click a table you'll see the following menu. In the Storage option you can create and manage partitions. Since we didn't define any partitions yet the Manage Partition option is disabled.

  •  

  •  

  • We go for Create Partition. First you'll get an introduction screen but after that you'll get this screen in which you can choose the partitioning  column and you can align the indexes. This is great because all this involved some serious scripting in SQL 2005.

  •  

  •  

  •  

  • Now you'll get some screens where you can choose or create new partition functions and schemes. When you opt for create you'll get this screen in which you can set the boundaries in a graphical manner.

  •  

  • Eventually you'll end up in this screen where you can execute the partition scripts or save them. The nice thing of the scripts is that they also run on SQL 2005. So if you need to do some partitioning on your 2005 server it pays of to install a SQL2008 server somewhere with your database and get some scripts :).

  •  

Comments

  • Anonymous
    March 23, 2009
    Is it possible to automate new partition creation when new record exceeds a predefined limit for last partition? Reply: Yes it is. Just check it regurarly in a stored procedure, scheduled with the agent. When the rowcount reaches the limit, create a new partition. Regards, Menzo

  • Anonymous
    March 23, 2009
    Thanks for you reply. Is there any feature of SQL Server 2008 a new partition get created automatically? Thanks, Sudesh

  • Anonymous
    March 23, 2009
    No, there isn't. Sliding window scenario's we have to code ourselves. There are simply to many scenarios and options to support automatically. On the bright side: it is not all that hard. Regards, Menzo

  • Anonymous
    August 30, 2011
    I wish you wouldnt do this, yes there may be a wizard but, there are too many factors to take into account before getting there, this makes it looks childs play it isnt