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 :).

  •