Partitioning Table in SQL Server:

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 );

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])

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

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.

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.

Partitioning Table in SQL Server:

Create database

Create Partition Function

Now create a partition scheme

Table Creation with partition

Partitioning on Existing Table

Rate

Share

Share

Rate

Partitioning Table in SQL Server:

Create database

Create Partition Function

Now create a partition scheme

Table Creation with partition

Partitioning on Existing Table

Rate

Share

Share

Rate

Related content

Book Review: Big Red - Voyage of a Trident Submarine

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

Inserting Markup into a String with SQL

Networking - Part 4

Speaking at Community Events - More Thoughts

Cookies on SQLServerCentral