/ Insights / Create a Table Partition in SQL Server 2012 Insights Create a Table Partition in SQL Server 2012 December 15, 2016 ConcurrencyPartitioning a SQL Table is a great way to organize your data for faster data retrieval. Partitions do exactly that, break down the Table into organized pieces based on criteria from your columns.Why partition?Primarily, performance reasons. Because the table is split up, data can be retrieved in a faster and more organized way when the query optimizer sees that partition. WHERE clause BIT data type = 1 would search through only that partition. This cuts back seeks and scans dramaticallyWhen should I partition?Partitioning sounds great but partitions are a rather large change to a table and can have implications on design and even make performance worse. Partitioning is best used with an extremely high level of records in the data. Before partitioning, ask yourself these questions:What query hints am I using?Have I optimized all of my queries?How much data is in this table?Am I using indexing correctly?Are my statistics updated?Have I done all I can to improve query performance?I you have gone through these questions and have done all you can to increase performance, than it is probably time to create a partition.How to Create a PartitionBefore you begin, ensure that you have a File Group dedicated to each of your partitions as well as a file in each partition group. The data needs to go somewhere so fulfill this requirement first.Open SQL Server and right click on the table you want to partition.Go to ‘Storage’.Click on ‘Create Partition’.Click ‘Next’ on the Wizard Page.Choose a column that you want to partition by.Name your new Partition Function.Name your new Partition Scheme. If one exists, chose the Partition Scheme that your partition should be used by.Decide between a Left or Right boundary. If you choose a Left boundary, your data will have a greater than or equals to condition for storing values in that partition or not. A Right boundary will be strictly a greater than condition. Example, Left boundary <= 32 Vacation Hours will put records with 32 hours or greater into the partition.Choose a File Group to partition to. Ensure you have more than one File Group to partition to, 1 for your conditional boundary records and another for your non conditional records.Click ‘Next’.Choose to either script out your selections to a code, run your partitioning immediately, or schedule out the partition in the future. Let’s choose Run immediately for now.Click ‘Next’.Click ‘Finish’.Your partition will run and you’re done!
