FAQ : What is Partition Scheme in SQL Server?

Once you have created the Partition Function then the next step is to create Partition Scheme. As already mentioned, One partition function may have one or more Partition Scheme. . The partition scheme defines how any resulting partitions will be stored on filegroups. You list the filegroups for a partition scheme in the same order that you want to have them map to the partitions defined by the partition function. Creating a partition scheme assumes that you have already created the filegroups. You can mention ALL TO keyword to create all the partition to a single filegroup.

Eg. From BOL
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Edit : Should Filegroup single file or multiple files considered as out of scope in this context. But selecting single filegroup VS multiple filegroup and Single files per filegroup VS multiple files also a significant decision to be made in terms of performance.

FAQ : What is Partition Function in SQL Server?

Partition functions are special kind of objects (of course created by user) created in a database which can be used only by partition scheme. Partition function contains a data type , that must be the same data type as the partition column chosen in the table. Partition Function as such not directly related to any table.

NOTE: Even though Partition Function to Partition scheme is One to Many, in sliding window scenario moving data between in and out of partition is a common requirement, which require Partition function modification. So practically it makes each Partition table should have exclusive partition function and scheme.
Partition functions are not user defined functions.

Here are few of facts to be known about partition functions• Partition functions are not listed as database objects in the sys.all_objects or sys.objects system tables; instead, you can find them listed in sys.partition_functions.
• Partition functions are not contained by a database schema.
• Special commands must be used for creating, altering, and dropping a partition function:
o CREATE PARTITION FUNCTION
o ALTER PARTITION FUNCTION
o DROP PARTITION FUNCTION
• Partition functions can be invoked interactively in Transact-SQL by using the $PARTITION function.

Partition Functions System Objects

• sys.partition_functions : Contains a row for each partition function.
• sys.partition_range_values : Contains a row for each range boundary value of a partition function of type R.
• sys.partition_parameters : Contains a row for each parameter of a partition function.
• $PARTITION. :Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
Eg. SELECT $PARTITION.RangePF1 (10) ;

Partition is not recommended for a table in following condition

1. If you want to have different indexing structure for READ ONLY and READ WRITE data then partition is not recommended.
2. If the table is not accessed much or do not required much index maintenance
3. If the query on the tables are filtered(WHERE condition) by some column which cannot be used for partitioned.
4. If you planning to use ONLINE INDEX REBUILD option you cannot use partition. You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

FAQ: How to determine partition suits for a table ?

There are few general guidelines for determining whether the partition feature is suited for a table or not. One point to be noted here is, if a table is not partitioned properly (ie. Not selected the most suitable partition column), though it will not adversely affect the performance but it will not give any performance benefit either. Remember that the query optimizer can limit the partitions that are scanned to resolve a query only if the query filters on the partitioning column. Precisely, just because the table is partitioned, we will not have performance benefit.

1. First of all, just because of a table is big, it should not be partitioned. Ie. Only size of the table cannot be "THE" criteria for partition.
2. If the index maintenance on a table is time consuming or make the table offline for the users, you may opt partition because re-indexing on only required partition is better than re-indexing the whole table. Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table.
3. You can go for partition if the data must be aged out of the table (archived) periodically and the current delete process is taking time or blocking other users.
4. If the table is loaded with new data periodically and the loading process takes time and the table data lends itself to a partition column based on ascending date or time we may go for partition.
5. Table backup of huge table can be managed smartly by using new backup features of sql server 2005 and partitioning.
6. To summarize, the best way to put this in single statement is, if you have any table which fits into SLIDING WINDOW scenario you must partition it.

FAQ: What all are the types of table partitions in SQL Server

There are two types of partitions. Horizontal and vertical.

Horizontal partition
Horizontal partition divides the data on subset of rows. Ie. Divide the data in such a way that all the rows of single year /month /week(Range) may be in one partition kind. In earlier versions of SQL Server this method was achieved by creating physical tables for each subsets and UNION the tables using a View(Partition View) and application access the data using Views.

Vertical partition
In Vertical Partition , the columns of a very wide table are spread across multiple physical tables containing distinct subsets of the columns with the same number of rows. The relation would be ONE TO ONE among the tables. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view.

FAQ : What is Table Partitioning in SQL Server?

Partitioning is the process of dividing data into small , more manageable chunks. We can divide the data horizontally (on rows ) or vertically (on columns) . Table partitioning can make very large tables and indexes easier to manage, and improve the performance of APPROPRIATELY FILTERED queries. Horizontal Partitioning is one of the new feature added in SQL Server 2005 (in earlier versions we used to create separate physical table and create a wrapper view which union all the tables which is also known as Partition View). When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes. You can then perform maintenance operations on individual partitions, and properly filtered queries will access only the correct partitions. But it is still one table as far as SQL Server is concerned