SQL Server Table partition (partitioned Table/data partitioning)

Server

Partitioned Table

Scalability is an important aspect of a database management system that provides table partitioning capabilities in terms of scalability in SQL Server 2005.

In fact, for the relational database products, the table, database and server data partitioning to provide large amount of data support is not new, but SQL Server 2005 provides a novel architecture capabilities for the file groups in the database to table partitioning. A horizontal partition divides a table into several smaller groupings based on the partition schema. The table partitioning feature is designed for very large databases (from hundreds of gigabytes to thousands of gigabytes or greater). The VLDB query performance has been improved through partitioning. By partitioning a wide range of partitioned column values, you can manage subsets of data and reassign them quickly and efficiently to other tables.

Imagine a rough electronic trading site, there is a table that stores historical transaction data for this site, which can be hundreds of billions of times, stored in a table in a previous version of SQL Server regardless of query performance or maintenance is a hassle, let's take a look at the SQL Server2005 how to improve performance and manageability:

--Create a test database to use, Demo

Use [master]

IF EXISTS (SELECT name from master.dbo.sysdatabases WHERE name = N ' DEMO ')

DROP DATABASE [DEMO]

CREATE DATABASE [DEMO]

-Because table partitioning uses a new architecture, using filegroups for table partitioning, we create the 6 filegroups that we will use to store transaction data for 6 time periods [<2000],[2001], [2002], [2003], [a], [>2005]

ALTER DATABASE Demo ADD FILEGROUP YEARFG1;

ALTER DATABASE Demo ADD FILEGROUP YEARFG2;

ALTER DATABASE Demo ADD FILEGROUP YEARFG3;

ALTER DATABASE Demo ADD FILEGROUP YEARFG4;

ALTER DATABASE Demo ADD FILEGROUP YEARFG5;

ALTER DATABASE Demo ADD FILEGROUP YEARFG6;

--Add files to these filegroups for physical data storage

ALTER DATABASE Demo ADD FILE (NAME = ' YEARF1 ', FILENAME = ' C:\ADVWORKSF1. NDF ') to FILEGROUP YEARFG1;

ALTER DATABASE Demo ADD FILE (NAME = ' YEARF2 ', FILENAME = ' C:\ADVWORKSF2. NDF ') to FILEGROUP YEARFG2;

ALTER DATABASE Demo ADD FILE (NAME = ' YEARF3 ', FILENAME = ' C:\ADVWORKSF3. NDF ') to FILEGROUP YEARFG3;

ALTER DATABASE Demo ADD FILE (NAME = ' YEARF4 ', FILENAME = ' C:\ADVWORKSF4. NDF ') to FILEGROUP YEARFG4;

ALTER DATABASE Demo ADD FILE (NAME = ' YEARF5 ', FILENAME = ' C:\ADVWORKSF5. NDF ') to FILEGROUP YEARFG5;

ALTER DATABASE Demo ADD FILE (NAME = ' YEARF6 ', FILENAME = ' C:\ADVWORKSF6. NDF ') to FILEGROUP YEARFG6;

--Here WE are associate the PARTITION FUNCTION to

--The CREATED FILEGROUP VIA A Partitioning SCHEME

Use DEMO;

Go

-------------------------------------------------------

--Create partition functions

-------------------------------------------------------

CREATE PARTITION FUNCTION YEARPF (datetime)

As

RANGE left for VALUES (' 01/01/2000 '

, ' 01/01/2001 '

, ' 01/01/2002 '

, ' 01/01/2003 '

, ' 01/01/2004 ')

-------------------------------------------------------

--Create a partition schema

-------------------------------------------------------

CREATE PARTITION SCHEME Yearps

As PARTITION YEARPF to (YEARFG1, Yearfg2,yearfg3,yearfg4,yearfg5,yearfg6)

--Create a table using this schema

CREATE TABLE Partitionedorders

(

ID INT not NULL IDENTITY (1,1),

DueDate DATETIME not NULL,

) on Yearps (DueDate)

--populating data for this table

DECLARE @DT datetime

SELECT @DT = ' 1999-01-01 '

--start looping, stop at ending date

while (@DT <= ' 2005-12-21 ')

BEGIN

INSERT into Partitionedorders VALUES (@DT)

SET @DT =dateadd (yy,1, @DT)

End

--Now we can look at the partition of the rows we just inserted.

SELECT *, $PARTITION. YEARPF (duedate) from Partitionedorders

--we can take a look at where we are now storing the data for the Partitionedorders table in this partition, and the distribution of the amount of data in these partitions

SELECT * from SYS. partitions WHERE object_id = object_id (' partitionedorders ')

--

--now, let's imagine, if we're going to be 2005 years away over time, and we want to go into a partition again, as we've already done, then we're going to recreate the table partitioning schema and then put the data into the new partition architecture, and the answer is no. Now let's see if we add a new partition.

--Change the partition schema definition language so that the next partition is used and now exists in the partitioned YEARFG6 partition so that the partition stores two partition of data.

ALTER PARTITION SCHEME Yearps

NEXT USED YEARFG6;

--Changing partition functions

ALTER PARTITION FUNCTION YEARPF ()

SPLIT RANGE (' 01/01/2005 ')

--now we can see which partition is the line we just inserted?

SELECT *, $PARTITION. YEARPF (duedate) from Partitionedorders

--we can take a look at where we are now storing the data for the Partitionedorders table in this partition, and the distribution of the amount of data in these partitions

SELECT * from SYS. partitions WHERE object_id = object_id (' partitionedorders ')