Partition in Sql Server

What is Partition

Partitioning is the database process or methodwhere very large tables and indexes are divided in multiple smaller and manageable parts. SQL Server 2005 allowsto partition tables using defined ranges and also provides management featuresand tools to keep partition tables in optimal performance.

The Attribute of Partition

Tables are partition based on column which will beused for partitioning and theranges associated to each partition. Pay attention to 4 points

1.   Example of this column will be incrementalidentity column, which can be partitioned in different ranges.

2.   Different ranges can be on differentpartitions,

3.   different partition can be on differentfilegroups,

4.   different partition can be ondifferent hard drive disk to improve performance.

Is Partition a good boy?

     good:

1. This will lead to high performance gain.Partitioning can increase performance when hard disk speed isperformance bottleneck, if CPU or RAM is bottleneck partitioning will not help much.
2. break down massive tables into smaller, easy-to-manage chunks. 
3.  put heavily accessed parts of the table onto fast storage, and less-accessed data onto slower, cheaper storage. 
4.  it’s completely transparent to applications: they don’t have to know the table is even partitioned.
5. Partitions can be set up very easily using schemesand functions as well can manage very easily humongous tables separate indexesfor each partition.

 

Follow me,Do next

-------------------------Step 1 : Create New Test Database with two different filegroups---------------------------
USE Master;
 GO
 
 IF EXISTS (
 SELECT name
 FROM sys.databases
 WHERE name = N'TestDB')
 DROP DATABASE TestDB;
 GO
 CREATE DATABASE TestDB
 ON PRIMARY
 (NAME='TestDB_Part1',
 FILENAME=
 'F:\SqlServerPartition\Primary\TestDB_Part1.mdf',
 SIZE=5, --primary file must be over 3M so that it can contains the　copy of model database
 MAXSIZE=100,
 FILEGROWTH=1 ),
 FILEGROUP TestDB_Part2
 (NAME = 'TestDB_Part2',
 FILENAME =
 'F:\SqlServerPartition\Secondary\TestDB_Part2.ndf',
 SIZE = 2,
 MAXSIZE=100,
 FILEGROWTH=1 );
 GO
 
------------------------------2 Create Partition Range Function-----------------------------------------
--　目的：Partition Function defines the range of values to be stored on different partition. 
--  例子：前10个存在第一个分区；后面的存在后面的分区里
USE TestDB;
 GO

 CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
 AS RANGE LEFT FOR
 VALUES (10);
 GO
 
 --- Step 3 : Attach Partition Scheme to FileGroups
 --- 目的：Partition function has to be attached with filegroups to be used in table partitioning
 --- 例子：将2的算法应用于分区1和分区2
 CREATE PARTITION SCHEME TestDB_PartitionScheme
 AS PARTITION TestDB_PartitionRange
 TO ([PRIMARY], TestDB_Part2);
 GO
 
 --Step 4 : Create Table with Partition Key and Partition Scheme（分区key--Partition Key）
 -- 目的：The table which is to be partitioned has to be created specifying column name to be used with partition scheme to partition tables in different filegroups.
 -- Following example demonstrates ID column as the Partition Key.
 USE TestDB;
 GO
 
 CREATE TABLE TestTable
 (ID INT NOT NULL,
 Date DATETIME)
 ON TestDB_PartitionScheme (ID);
 GO
 
 --Step 5 : (Optional/Recommended) Create Index on Partitioned Table
 --This step is optional but highly recommended. Following example demonstrates the creation of table aligned index. Here index is created using same Partition Scheme and Partition Key as Partitioned Table.
 USE TestDB;
 GO
 --- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
 CREATE UNIQUE CLUSTERED INDEX IX_TestTable
 ON TestTable(ID)
 ON TestDB_PartitionScheme (ID);
 GO
 
 --Step 6 : Insert Data in Partitioned Table
 --Insert data in the partition table. Here we are inserting total of 3 records. We have decided that in table partition 1 Partition Key ID will contain records from 1 to 10 and partition 2 will contain reset of the records. In following example record with ID equals to 1 will be inserted in partition 1 and rest will be inserted in partition 2.
 USE TestDB;
 GO

 INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
 VALUES (1,GETDATE());
 INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
 VALUES (11,GETDATE());
 INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
 VALUES (12,GETDATE());
 GO
 
 
 ----------------------------------------------------
 --Step 7 : Test Data from TestTable
 --Query TestTable and see the values inserted in TestTable.
 USE TestDB;
 GO
 --- Step 7 : Test Data from TestTable
 SELECT *
 FROM TestTable;
 GO
 
 --Step 8 : Verify Rows Inserted in Partitions
 --We can query sys.partitions view and verify that TestTable contains two partitions and as per Step 6 one record is inserted in partition 1 and two records are inserted in partition 2.
 USE TestDB;
 GO
 
 SELECT *
 FROM sys.partitions
 WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
 GO

 

summary:

           partition is good for hard IO,and exstrat the data we operate repeatly,such as the recent data with high visit times.