jump to navigation

Create and manage partitioned tables in SQL Server May 5, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags:
trackback

In this post I would like to explain with some hands-on examples how to create a partitioned table in SQL Server and then manage the partitions.

I know there are samples of how to do that in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.

Many people have found useful my style of posting. They feel they can follow the steps and master the technology/topic.

We all know that at some point our database is going to grow and possibly become huge. In a database that it is e.g 3 Terabytes in size, we could have tables with million of rows.

When we execute queries against tables with million of rows then we will probably get slow response times.We could partition the tables which means dividing the rows in various partitions based on column values.When you execute queries against tables that are partitioned may be much faster because SQL Server is able to search through a single partition to find the records needed instead of the entire table.

If you want to create a partitioned table you must.

1) Firstly, we have to select the table that must be partitioned.

2) Then we need to select the column on which the partition will take place.

3) Then we need to create a partition function. This is a function that specifies how to partition the table by defining partition key boundaries for each subset of data.

4) Then we need to create as partition scheme.It maps individual partitions to filegroups.

When creating a partition scheme we must bear in mind the following

  • Each partition must map to a filegroup
  • A single filegroup can contain more than one partition
  • Each partition can have its own filegroup
  • All partitions can map to the same filegroup
  • Defining extra filegroups allows for adding new partitions in the future
  • A partition scheme uses only one partition function

Let’s move on to the hands-on examples.

We need an instance of SQL Server running in our machine. I have SQL Server 2008 R2 Developer edition installed in my machine.

In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it from here . This is a trial version.

We need a database as well. I will create a brand new database later on.

1) Launch SQL Server Management studio and connect to the desired instance of the SQL Server.We will create a new database and then we will create 4 filegroups that will map to 4 physical files.

In an new query window type the following T-SQL statements and execute them


USE master
GO
CREATE DATABASE UNIVERSITY
GO

ALTER DATABASE UNIVERSITY ADD FILEGROUP UNIVERSITY_DATA_1
GO
ALTER DATABASE UNIVERSITY ADD FILEGROUP UNIVERSITY_DATA_2
GO
ALTER DATABASE UNIVERSITY ADD FILEGROUP UNIVERSITY_DATA_3
GO
ALTER DATABASE UNIVERSITY ADD FILEGROUP UNIVERSITY_DATA_4
GO

ALTER DATABASE UNIVERSITY
ADD FILE
( NAME = UNIVERSITY1,
FILENAME = 'C:\DATA\UNIVERSITY_1.ndf',
SIZE = 100MB,
MAXSIZE = 300MB,
FILEGROWTH = 50MB)
TO FILEGROUP UNIVERSITY_DATA_1
GO

ALTER DATABASE UNIVERSITY
ADD FILE
( NAME = UNIVERSITY2,
FILENAME = 'C:\DATA\UNIVERSITY_2.ndf',
SIZE = 100MB,
MAXSIZE = 300MB,
FILEGROWTH = 50MB)
TO FILEGROUP UNIVERSITY_DATA_2
GO

ALTER DATABASE UNIVERSITY
ADD FILE
( NAME = UNIVERSITY3,
FILENAME = 'D:\DATA\UNIVERSITY_3.ndf',
SIZE = 100MB,
MAXSIZE = 300MB,
FILEGROWTH = 50MB)
TO FILEGROUP UNIVERSITY_DATA_3
GO

ALTER DATABASE UNIVERSITY
ADD FILE
( NAME = UNIVERSITY4,
FILENAME = 'd:\DATA\UNIVERSITY_4.ndf',
SIZE = 100MB,
MAXSIZE = 300MB,
FILEGROWTH = 50MB)
TO FILEGROUP UNIVERSITY_DATA_4
GO

2) I will create a table that has information regarding student registration.I will create a column in that table that is called “RegistrationDate”.This will be the partition column.Note that I have not created the table yet. This is a good column for the partition because we can partition on different date ranges e.g put the registration dates for the past year in one partition and the registration dates for the period 2009 in another.

3) Now we need to create the partition function.In the same or a new query window type the following T-SQL statements and execute them


USE University
GO

CREATE PARTITION FUNCTION pf_regdate (DATETIME)
AS RANGE RIGHT FOR VALUES ('2008-01-01','2009-01-01','2010-01-01')

--by running this command you can see in that partition will go a record with registration date of 2009-06-01

SELECT $PARTITION.pf_regdate('2009-06-01')

I create 4 partitions. All the values that are less than 01-01-2008 will go into their own partition (1st partition).All the values greater than 01-010-2010 will go into the last partition (4th partition)

4)  We now need to create the partition scheme.In the same or a new query window type the following T-SQL statement and execute it


CREATE PARTITION SCHEME ps_regdate AS PARTITION pf_regdate
TO (UNIVERSITY_DATA_1,UNIVERSITY_DATA_2,UNIVERSITY_DATA_3,UNIVERSITY_DATA_4)

I need one partition per filegroup at a minimum.

5) Now we must define that we want to create a table on a partition scheme for the specified column.In the same or a new query window type the following T-SQL statements and execute them


CREATE TABLE StudentRegistration
(
id INT IDENTITY(1, 1)NOT NULL,
discount tinyint NOT NULL ,
Paid BIT NOT NULL ,
Confirmed BIT NOT NULL ,
RegistrationDate DATETIME NOT NULL
CONSTRAINT pk_registration PRIMARY KEY (id,RegistrationDate)
) ON ps_regdate (RegistrationDate)

GO

ALTER TABLE StudentRegistration
WITH CHECK

ADD CONSTRAINT CK_discount
CHECK (discount >= 20 AND discount <= 50)

6 )Now let’s insert some data into the table.I will insert only 10 records but as I said in the beginning we apply partitioning on tables that have millions of rows.

In the same or a new query window type the following T-SQL statements and execute them


USE University

SET IDENTITY_INSERT StudentRegistration ON
INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed],

[RegistrationDate]) VALUES (94495, 25, 1, 1, '02-03-2008')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94496, 48, 1, 0, '02-03-2009')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94500, 42, 1, 1, '03-03-2005')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94501, 42, 1, 1, '04-03-2005')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94502, 32, 0, 1, '01-07-2010')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94502, 32, 0, 1, '01-07-2010')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94503, 32, 0, 1, '01-07-2011')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94504, 32, 0, 1, '01-07-2006')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94506, 32, 0, 1, '01-07-2008')

INSERT [dbo].[StudentRegistration] ([id], [discount], [Paid], [Confirmed], [RegistrationDate])
VALUES (94509, 32, 0, 1, '07-07-2009')

SET IDENTITY_INSERT StudentRegistration OFF

7) We can see the newly created partitions and the rows in each of them by executing the T-SQL statement below.


SELECT OBJECT_Name(object_id),
* FROM sys.partitions
WHERE OBJECT_Name(object_id) = 'StudentRegistration'

Have a look at the picture below to see what I mean

8) Νο Ι am going to demonstrate how to manage partitions.I am going to show you how to archive a partition into a separate table and then split an existing partition in two.

Let’s assume that we want to archive the records from the first partition (rows with registration date earlier than 1-1-2008) into a separate table.

Firstly I need to create the table and then move the records.Type the following T-SQL statements in a new query window and execute them


USE UNIVERSITY
GO

CREATE TABLE StudentRegistrationHistory
(
id INT IDENTITY(1, 1)NOT NULL,
discount tinyint NOT NULL ,
Paid BIT NOT NULL ,
Confirmed BIT NOT NULL ,
RegistrationDate DATETIME NOT NULL
CONSTRAINT pk_registration_history PRIMARY KEY (id,RegistrationDate)
) ON UNIVERSITY_DATA_1

ALTER TABLE dbo.StudentRegistration SWITCH PARTITION 1 TO StudentRegistrationHistory

--will see 3 records with registration date earlier than 1-1-2008
SELECT * FROM StudentRegistrationHistory

--all the other records remaining will be in the StudentRegistration table in the same partitions

SELECT * FROM StudentRegistration

I create the table-history/archive table.

I accomplish the move from partition 1 of the first table to StudentRegistrationHistory  by executing the statement

“ALTER TABLE dbo.StudentRegistration SWITCH PARTITION 1 TO StudentRegistrationHistory ”

Then I execute SELECT statements to see that the move actually happened.Partition 1  exists but has no records

9) Moving on let’s demonstrate how to split an existing partition to two partitions. I want to change my last partition that holds records from 1-1-2010 onward to two partitions that have records from 1-1-2010 to 1-1-2011 and another one from 1-1-2011 to present.

Type the following T-SQL statements in a new query window and execute them


USE UNIVERSITY
GO

ALTER DATABASE UNIVERSITY ADD FILEGROUP UNIVERSITY_DATA_5
GO

ALTER DATABASE UNIVERSITY
ADD FILE
( NAME = UNIVERSITY5,
FILENAME = 'D:\DATA\UNIVERSITY_5.ndf',
SIZE = 100MB,
MAXSIZE = 300MB,
FILEGROWTH = 50MB)
TO FILEGROUP UNIVERSITY_DATA_5
GO

ALTER PARTITION SCHEME ps_regdate

NEXT USED UNIVERSITY_DATA_5

ALTER PARTITION FUNCTION pf_regdate()
SPLIT RANGE ('2011-01-01')

SELECT OBJECT_Name(object_id),
* FROM sys.partitions
WHERE OBJECT_Name(object_id) = 'StudentRegistration'

I create a new filegroup.Then I add a file to the newly created filegroup.Then I change the partition scheme  and finally split the records into the new partition using the SPLIT operator.

When I execute the last statement

(SELECT OBJECT_Name(object_id),
* FROM sys.partitions
WHERE OBJECT_Name(object_id) = ‘StudentRegistration’)

I see that I have 5 partitions now.Have a look at the picture below

10) Now I want to merge the records from the partition with values from  1-1-2008 to 1-1-2009 with the partition with values from 1-1-2009  to 1-1-2010.

Type the following T-SQL statements in a new query window and execute them


USE UNIVERSITY
GO

ALTER PARTITION FUNCTION pf_regdate()
MERGE RANGE ('2009-01-01')

SELECT OBJECT_Name(object_id),
* FROM sys.partitions
WHERE OBJECT_Name(object_id) = 'StudentRegistration'

I use the MERGE operator to achieve that by altering the partitioning function.When I execute the last statement I see that I have 4 partitions now.

Hope it helps!!!

Comments»

1. Dot Net Rules : Create and manage partitioned tables in SQL Server - May 5, 2012

[…] useful my style of posting. They feel they can follow the steps and master the technology/topic. (read more) Share Posted: Σάββατο, 5 Μαΐου 2012 11:08 μμ από το μέλος […]

2. Dan - November 27, 2012

Good one & very helpful

3. Claus - April 9, 2013

Thankyou for a good demonstration. Just what I was looking for :-)

4. SQL Server 2012 Table Partitioning | SQL DBA Talk - April 10, 2014

[…] SQL Server 2012 Table Partitioning Posted by fofo DOT NET RULES […]

5. Febri Harsanto - June 10, 2014

Good explanation. Thanks

6. HP - April 5, 2016

This article is so simple but very much effective. I read few articles for Partitioning but this one is so straightforward and easy to understand, Great job.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: