What are partitions and why use them?

To improve the scalability and manageability of large tables and tables that have varying access patterns. Typically, you create tables to store information about an entity, such as customers or sales, and each table has attributes that describe only that entity. While a single table for each entity is the easiest to design and understand, these tables are not necessarily optimized for performance, scalability, and manageability, particularly as the table grows larger.

While the size of a very large database (VLDB) is measured in hundreds of gigabytes, or even terabytes, the term does not necessarily indicate the size of individual tables within the database. because of this the performance of system getting slow and more problems got occured for maintenance. Because of this server limits both the availability and the scalability of the server.

Additionally, when large sets of data are being used in different ways, frequent maintenance operations are performed on static data. This can have costly effects, such as performance problems, blocking problems, backups (space, time, and operational costs) as well as a negative impact on the overall scalability of the server.

How can partitioning help?

When tables and indexes become very large, partitioning can help by partitioning the data into smaller, more manageable sections.

The definition of the partitioned set is customized, defined, and managed by your needs.

Microsoft SQL Server 2005 allows you to partition your tables based on specific data usage patterns using defined ranges or lists. SQL Server 2005 also offers numerous options for the long-term management of partitioned tables and indexes by the addition of features designed around the new table and index structure.

If a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations. The performance of large-scale operations across extremely large data sets (for instance many million rows) can benefit by performing multiple operations against individual subsets in parallel.

SQL Server 2005 has simplified partitioning with administration, development, and usage in mind. Some of the performance and manageability benefits are:

•	Simplify the design and implementation of large tables that need to be partitioned for performance or manageability purposes.
•	Load data into a new partition of an existing partitioned table with minimal disruption in data access in the remaining partitions. 
•	Load data into a new partition of an existing partitioned table with performance equal to loading the same data into a new, empty table. 
•	Archive and/or remove a portion of a partitioned table while minimally impacting access to the remainder of the table. 
•	Allow partitions to be maintained by switching partitions in and out of the partitioned table.
•	Allow better scaling and parallelism for extremely large operations over multiple related tables.
•	Improve performance over all partitions.
•	Improve query optimization time because each partition does not need to be optimized separately.

DEFINITIONS AND TERMINOLOGY

To implement partitions in SQL Server 2005, you must be familiar with a few new concepts, terms, and syntax.To understand these new concepts, let's first review a table's structure with regard to creation and placement.

Range Partitions Range partitions are table partitions that are defined by specific and customizable ranges of data. The range partition boundaries are chosen by the developer, and can be changed as data usage patterns change. Typically, these ranges are date-based or based on ordered groupings of data.

Defining the Partitioning Key

The first step in partitioning tables and indexes is to define the data on which the partition is keyed. A partitioning key is a column (or group of columns) that is used to determine the partition in which a particular row of data is stored. A partitioning key is defined on a table using the CREATE TABLE statement. If a partitioning key is not defined for a table in a table space that is divided across more than one database partition in a database partition group, one is created by default from the first column of the primary key. If no primary key is specified, the default partitioning key is the first non-long field column defined on that table.

Choosing a good partitioning key is important. You should take into consideration:

•How tables are to be accessed

•The nature of the query workload

•The join strategies employed by the database system.

Index Partitioning

Even you can partition indexes. When a table and its indexes are aligned, then SQL Server can move partitions in and out of partitioned tables more effectively, because all related data and indexes are divided with the same algorithm.

Special Conditions for Partitions: Split, Merge, and Switch

You can use above option for creting partition.

You can use the ALTER TABLE statement with the new split option to add another partition to the table.

When a partition is split, data can be moved to the new partition; but to preserve performance, rows should not move.

Steps for disign Partitioned Tables

1) Determine If Object Should Be Partitioned

2) Determine Partitioning Key and Number of Partitions

3) Determine If Multiple Filegroups Should Be Used

4) Create Filegroups

5) Create the Partition Function for a Range Partition

6) Create the Partition Scheme

7) Create the Partitioned Table

8) Create Indexes: Partitioned or Not?

Partitioning a SQL Server database table is a three-step process:

1. Create the partition function
2. Create the partition scheme
3. Partition the table

The remainder of this article explores each of those steps in further detail.

Step 1: Creating a Partition Function

The partition function defines [u]how[/u] you want SQL Server to partition the data. At this point, we’re not concerned with any particular table, we’re just generically defining a technique for splitting data.

We define the partitions by specifying the boundaries of each partition. For example, suppose we have a Customers table that contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We might decide to partition that table into four equally spaced partitions, using the following partition function (I’ll call it customer_partfunc):

CREATE PARTITION FUNCTION customer_partfunc (int)
 AS RANGE RIGHT
 FOR VALUES (250000, 500000, 750000)

These boundaries define four partitions. The first contains all values less than 250,000. The second contains values between 250,000 and 499,999. The third contains values between 500,000 and 749,999. All values greater than or equal to 750,000 go in the fourth partition.

Notice that I used the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than [u]or equal to[/u] 250,000; the second partition would have included values between 250,001 and 500,000, and so on.

Step 2: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

CREATE PARTITION SCHEME customer_partscheme
 AS PARTITION customer_partfunc
 TO (fg1, fg2, fg3, fg4)

Notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. That’s where the power of reusability comes into play. We could use this partition scheme (or just the partition function, if we desired) on any number of database tables.

Step 3: Partitioning a Table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

For example, if you wanted to create a customer table using our partition scheme, you would use the following Transact-SQL statement:

CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
 ON customer_partscheme (CustomerNumber)

That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!

For Example Plz Click :: http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

SUMMARY

SQL Server 2005 offers a way to easily and consistently manage large tables and indexes through partitioning, which allows you to manage subsets of your data outside of the active table. This provides simplified management, increased performance, and abstracted application logic, as the partitioning scheme is entirely transparent to the application. When your data has logical groupings (ranges or lists) and larger queries must analyze that data within these predefined and consistent ranges, as well as manage incoming and outgoing data within the same predefined ranges, then range partitioning is a simple choice. If you are looking at analysis over large amounts of data with no specific range to use or if all queries access most, if not all of the data, then using multiple filegroups without any specific placement techniques is an easier solution that will still yield performance gains.

For detail refrence click

Thanks

Data partitioning improves the performance, reduces contention and increases availability of data.

This allows you to operate on a partition even with performace critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available, all the data in that partition are available for quering, even if the restore is not yet fully completed.

A Table can be partitioned based on any column in the table, that column called as the partition key.

Partitioning a SQL Server database table is a three-step process: 1). Create the partition function 2). Create the partition scheme 3). Partition the table

You can create a partitioning strategy in very large tables in order to simplify administration, enhance performance, create an archiving strategy to purge out data from the database and manage partitions merging or splitting them to provision space for future data. You must take care of table design and choose the correct partition key as well as index.

SQL Server - Partitioning

Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. Maintenance tasks, such as rebuilding indexes or backing up a table, can run more quickly.

Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Putting a table on one physical drive and related tables on a separate drive can improve query performance because, when queries that involve joins between the tables are run, multiple disk heads read data at the same time. SQL Server filegroups can be used to specify on which disks to put the tables.

Different Partitioning:

Hardware Partitioning-Hardware partitioning designs the database to take advantage of the available hardware architecture.

Multiprocessors that enable multiple threads of operations, permitting many queries to run at the same time. Alternatively, a single query may be able to run faster on multiple processors by letting components of the query run at the same time. For example, each table referenced in the query can be scanned at the same time by a different thread.

Horizontal Partitioning-Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows.For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.

Vertical Partioning-Vertical partitioning divides a table into multiple tables that contain fewer columns. The two types of vertical partitioning are normalization and row splitting:

Normalization is the standard database process of removing redundant columns from a table and putting them in secondary tables that are linked to the primary table by primary key and foreign key relationships.

Row splitting divides the original table vertically into tables with fewer columns. Each logical row in a split table matches the same logical row in the other tables as identified by a UNIQUE KEY column that is identical in all of the partitioned tables. For example, joining the row with ID 712 from each split table re-creates the original row.

Vertical partitioning should be considered carefully, because analyzing data from multiple partitions requires queries that join the tables. Vertical partitioning also could affect performance if partitions are very large.

Partitioning enhancements in SQL Server 2008

Thread allocation Behaviour: In SQL Server 2005, one thread is allocated per partition when multiple partitions are queried. These threads then work in parallel to retrieve the rows to satisfy the query. If only one partition is queried, however, SQL Server 2005 allocates as many threads as are available to the one partition. As you can quickly see, there is a vulnerability when it comes to queries that query very few, but more than 1 partition.

SQL Server 2008 behavior changes the way in which threads are allocated. It allocates all available threads to each partition relevant to the query in a round robin fashion. This results in fast and consistent query execution, no matter how many partitions are queried.

Partition Query behaviour: SQL Server 2005 only allows lock escalation to the table level. Let’s say your query eliminates 75% of a table’s partitions, but scans all of the remaining partitions (1/4 of the table). If SQL Server decides to escalate the lock, all other queries will be locked out even if they are querying completely different partitions.

SQL Server 2008 provides a table option to override this default behavior. Note that the default is still to escalate to table locks (at least for now) so this option will need to be changed to take advantage of partition-level lock escalation.

Aligned Indexes in partitioning:

Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table. As a result, the index is partitioned in essentially the same manner as the table. This makes the index aligned with the table.

Aligning an index with a partitioned table is particularly important if you anticipate that it will expand by taking on additional partitions, or that it will be involved in frequent partition switches.When a table and its indexes are in alignment, SQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes.

Partitioning Unique Indexes When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key.

Partitioning Clustered Indexes When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL Server adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column.

Partitioning NonClustered Indexes When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonunique, nonclustered index, SQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. SQL Server does not add the partitioning column to the index if it is already present in the index.

Memory Limitations and Partitioned Indexes When SQL Server performs sorting to build partitioned indexes, it first builds one sort table for each partition. It then builds the sort tables either in the respective filegroup of each partition or in tempdb, if the SORTINTEMPDB index option is specified.

Each sort table requires a minimum amount of memory to build. When you are building a partitioned index that is aligned with its base table, sort tables are built one at a time, using less memory. However, when you are building a nonaligned partitioned index, the sort tables are built at the same time. As a result, there must be sufficient memory to handle these concurrent sorts. The larger the number of partitions, the more memory required.

Partitioning is the database process or method where very large tables and indexes are divided in multiple smaller and manageable parts.

SQL Server only supports one type of partitioning, which is Range Partitions. More specifically I should say 'Horizontal Range Partitions'. This the partitioning strategy in which data is partitioned based on the range that the value of a particular field falls in. The other partitioning types are reference, hash, list etc. partitions, which are not supported currently in SQL Server.

SQL Server 2005 allows to partition tables using defined ranges and also provides management features and tools to keep partition tables in optimal performance.

Tables are partition based on column which will be used for partitioning and the ranges associated to each partition. Example of this column will be incremental identity column, which can be partitioned in different ranges. Different ranges can be on different partitions, different partition can be on different filegroups, and different partition can be on different hard drive disk to improve performance.

Partitions can be set up very easily using schemes and functions as well can manage very easily humongous tables separate indexes for each partition. This will lead to high performance gain. Partitioning can increase performance when hard disk speed is performance bottleneck, if CPU or RAM is bottleneck partitioning will not help much.

http://blog.sqlauthority.com/2008/01/24/sql-server-2005-introduction-to-partitioning/

Horizontal Partitioning

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.

There are three major steps for implementing horizontal partitioning in SQL Server

(1) Create a partitioning function. It will have criteria to partition the data in the partitions.
(2) Create partition schemes to map the created partition function to file groups. It is related to the physical storage of data on disk.
(3) Create the table by linking it to the partition scheme and also to the partition function. A partition column will be used for this purpose.

Partition column is always a single column or computed column or persisted computed column by combining more than one columns. Any data type that may be used for index key is eligible for partition column except TIMESTAMP data type.

(1) Create the partition function

The Partition Function is the function that defines the number of partitions using 'RANGE'.

Based on Boundary values for partitions the partition function divide into two types:

Left: The first value is the maximum value of the first partition.
Right: The first value is the minimum value of the second partition.

Syntax

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

(2) Create the partition scheme

This is the physical storage scheme that will be followed by the partition. To define scheme, different file groups are specified, which would be occupied by each partition. It must be remembered that all partitions may also be defined with only one file group.

Syntax

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

The following permissions can be used to execute CREATE PARTITION SCHEME:

ALTER ANY DATASPACE permission, CONTROL or ALTER permission on the database in which the partition scheme is being created, CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition scheme is being created.

If all the partitions map to the same filegroup, use the ALL keyword. But if multiple, but not all, partitions are mapped to the same filegroup, the filegroup name must be repeated,

(3) Partition the table

After creation of a partition scheme, a table may be defined to follow that scheme. In this case the table is called PARTITIONED. A partitioned table may have a partitioned index. Partition aligned index views may also be created for this table. These index and view may be based on different partition strategy (partition function and partition scheme).

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ] [ ; ]

PARTITIONED INDEX

We can also partition our indexes, and this should contribute to improved performance. If indexes are partitioned they serve as the local indexes for each partition. We can also go with Global indexes on a partitioned table without caring about the different partitions of the table.

CREATE NONCLUSTERED INDEX MyNonClusteredIndex ON dbo.MyTable(MyID1)
ON MyPartitionScheme(MyID2);

Horizontal-partition-database-table Example
http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

GUI SUPPORT FOR PARTITIONING

Great GUI support is provided in SQL Server 2008 for partitioning. If you see the properties of a table, you can easily find partition related properties under the Storage tab.

How SQL Server managing a partitioned object?

1. Split
2. Merge
3. Switch

Remember that these operations are meta data operations and do not involve any movement of data.

ADDING A NEW PARTITION

This operation is supported to accommodate the continuous changes when a new Partition needs to be added if the already existing partitions are very large. This is technically called a Partition Split.

ALTER PARTITION FUNCTION MyPartitionFunction()
SPLIT RANGE (5000)

But before splitting, a new file group must be created and added to the partition scheme using the partition function being modified, otherwise, this statement would cause an error while executing.

REMOVING AN EXISTING PARTITION

The Merge operation is used to remove an existing partition. The syntax of MERGE statement is nearly the same as the SPLIT statement. This statement would remove the partition created in SPLIT command shown before this.

ALTER PARTITION FUNCTION MyPartitionFunction ()
MERGE RANGE (5000)

SWITCHING A PARTITION

This operation is used to switch a partition in or out of a partitioned table. It must be remembered that for switching in, the already existing partition must be empty within the destination table. In the following example, we are switching in a table MyNewPartition as 4th partition to MyPartitionedTable.

ALTER TABLE MyNewPartTable switch TO MyPartitionedTable PARTITION 4

http://www.sqlservercentral.com/articles/partition/64740/#

Notes

• Partitions cannot span servers or instances. Partitions have to be in the same instance and in the same database. Partitioning therefore is a scale-up solution.
• The partition scheme definition defines on which filegroup a partition resides.
• You can partition an existing table by creating a clustered index (or rebuilding an existing clustered index) on your new Partition Scheme. This will effectively partition your data as the leaf level of a clustered index is essentially the data.
• Partitioning an existing table is a strictly offline operation
• SWITCH'ing partitions OUT or IN in only a few seconds. SWITCH'ing partitions OUT or IN is a truly "meta data" operation in that although the partitioned data has magically moved from the partitioned table to the SWITCH table or vice versa there hasn't actually been any movement of data on the disk or inside the data file.
• MERGE or SPLIT operation may take much much longer than the few seconds expected. Altering a partition function is an offline operation and can also result in movement of data on disk and so become extremely resource intensive.

http://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/

Partition using Wizard in sqlserver2008
http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx

SQL Server 2005 offers a way to easily and consistently manage large tables and indexes through partitioning, which allows you to manage subsets of your data outside of the active table. This provides simplified management, increased performance, and abstracted application logic, as the partitioning scheme is entirely transparent to the application. When your data has logical groupings (ranges or lists) and larger queries must analyze that data within these predefined and consistent ranges, as well as manage incoming and outgoing data within the same predefined ranges, then range partitioning is a simple choice. If you are looking at analysis over large amounts of data with no specific range to use or if all queries access most, if not all of the data, then using multiple filegroups without any specific placement techniques is an easier solution that will still yield performance gains.