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 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.
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:
- Create the partition function
- Create the partition scheme
- 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/
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