Partitioning Table in SQL Server:

UmaShankar-Patel, 2012-12-10

Partitioning Table in SQL Server:

 When your database has large tables. Then partitioning will be beneficial

in terms of performance, scalability and maintainability. Sql Server

partitioning allows to spread data on various physical disks leveraging the concurrent

performance of those disks to optimize query performance. 

To use partitioning we create a

database with multiple file groups. Here is an example with three file groups.

The first is Primary Data file group. Others are Secondary file group. Secondary data files are optional,

are user-defined, and store user data. Secondary files can be used to spread

data across multiple disks by putting each file on a different disk drive.

Create database

CREATE DATABASE partioningDB

ON PRIMARY

(

NAME=‘PRIMARY’,

FILENAME=

‘C:\FG1\fg1.mdf’,–Priamry data file

SIZE=6,

MAXSIZE=600,

FILEGROWTH=1

),

FILEGROUP fg2

(NAME = ‘fg2’,

FILENAME =

‘D:\fg2\fg2.ndf’,–ndf file is secondary data files make up all the data files, other than

the primary data file

SIZE = 2,

MAXSIZE=100,

FILEGROWTH=1 )

,

FILEGROUP fg3

(NAME = ‘fg3’,

FILENAME =

‘D:\fg3\fg3.ndf’,–ndf file is secondary data files make up all the data files, other than

the primary data file

SIZE = 2,

MAXSIZE=100,

FILEGROWTH=1 );

GO

Create Partition Function

Now we need to create a partition

range function with following syntax.

CREATE PARTITION FUNCTION

partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [

boundary_value [ ,…n ] ] )

[ ; ]

Ex.

CREATE PARTITION FUNCTION

func_partition (int)

 AS RANGE RIGHT

 FOR VALUES (10,50)

FOR VALUES (10,50) defines Boundaries.

These boundaries define three partitions. The first contains all values less

than 10. The second contains values between 10 and 49. Third contains rest of

values.

Now create a partition scheme

You need to create a scheme that defines where you want to

partition data. Syntax to create scheme is

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [

,…n ] )

[ ; ]

Ex.

CREATE PARTITION SCHEME scheme_partition

 AS PARTITION

func_partition

 TO ([PRIMARY], fg2, fg3)

Partition is created on primary and fg2, and fg3 file groups.

Now to partitioning a table you

need to create a table or alter table

Table Creation with partition

CREATE TABLE Person (fname nvarchar(40), lname nvarchar(40), [uid] int)

 ON scheme_partition ([uid])

 Partitioning on Existing Table

ALTER TABLE Person ADD CONSTRAINT [PK_uid] PRIMARY

KEY CLUSTERED

(

[uid] ASC

)

WITH

(     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE

= OFF, SORT_IN_TEMPDB = OFF,

      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

)

      ON scheme_partition([uid])

GO

Now partitioning of table has been done. To test insert

some data in table

insert into Person values (‘A’,‘a’,1)

insert into Person values (‘B’,‘b’,2)

insert into Person values (‘C’,‘c’,3)

insert into Person values (‘D’,‘d’,23)

insert into Person values (‘E’,‘e’,24)

insert into Person values (‘F’,‘f’,25)

insert into Person values (‘G’,‘g’,10)

insert into Person values (‘H’,‘h’,60)

insert into Person values (‘I’,‘i’,58)

insert into Person values (‘J’,‘j’,54)

insert into Person values (‘K’,‘k’,55)

Now to verify your data run this query
SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)=‘Person’;

Result:


See first partition have 3 rows means UID<10. As (1, 2,

and 3)

Then Second contains 4 rows UID<50. (23, 24, 25, 10)

Then Third contains 4 rows UID>=50 as (55, 54, 58, 60)

Fetch Data from Table

Select *from Person

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads