nggak ngerti kenapa nih blog gak bisa di-post di sqlserver-indo, akhirnya posting di geeks.
Partition Table adalah sebuah feature yang mulai ada sejak SQL Server 2005. Dengan feature ini, kita dapat membagi data (biasanya yang ukurannya besar) ke dalam unit-unit yang dapat kita sebar di lebih dari satu filegroup dalam satu database. Pembagian ini akan berakibat pada peningkatan performance pada saat proses query terhadap data ataupun pada saat melakukan transaksi terhadap data.
Dua kata kunci yang harus kita pahami sebelum mempartisi table adalah Partition Function dan Partition Scheme. Partition Function digunakan untuk mendefinisikan bagaimana row-row yang terdapat di suatu table di-mapped kedalam sebuah set partisi berdasarkan nilai dari column tertentu yang disebut partitioning column. Sedangkan partition scheme me-map setiap partisi yang didefinisikan di Partition Function ke dalam sebuah filegroup.
Jika pada SQL Server 2005 kita harus membuat Partition Table menggunakan T-SQL, maka di SQL Server 2008 terdapat wizard yang dapat kita gunakan untuk membuatnya. Pada contoh berikut saya akan membuat sebuah database dengan beberapa filegroup, membuat sebuah table, lalu mempartisi nya berdasarkan tanggal dan menyimpan datanya ke dalam filegroup menggunakan wizard tadi.
Langkah pertama, membuat database dengan nama PartitionDB ditambah 3 buah filegroup.
CREATE DATABASE [PartitionDB] ON PRIMARY
( NAME = N'PartitionDB_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_Data.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [PartitionDB_200901_FileGroup]
( NAME = N'PartitionDB_200901_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_200901_Data.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [PartitionDB_200902_FileGroup]
( NAME = N'PartitionDB_200902_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_200902_Data.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [PartitionDB_200903_FileGroup]
( NAME = N'PartitionDB_200903_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_200903_Data.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PartitionDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [PartitionDB] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [PartitionDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PartitionDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PartitionDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PartitionDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PartitionDB] SET ARITHABORT OFF
GO
ALTER DATABASE [PartitionDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PartitionDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [PartitionDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PartitionDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PartitionDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PartitionDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [PartitionDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PartitionDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PartitionDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PartitionDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PartitionDB] SET DISABLE_BROKER
GO
ALTER DATABASE [PartitionDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PartitionDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PartitionDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PartitionDB] SET READ_WRITE
GO
ALTER DATABASE [PartitionDB] SET RECOVERY FULL
GO
ALTER DATABASE [PartitionDB] SET MULTI_USER
GO
ALTER DATABASE [PartitionDB] SET PAGE_VERIFY CHECKSUM
GO
USE [PartitionDB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [PartitionDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
Selanjutnya, buat sebuah table dengan nama PartitionTab di database PartitionDB dengan 4 column, yaitu ID, Col1, Col2, dan ModifiedDate
USE [PartitionDB]
GO
/****** Object: Table [dbo].[PartitionTab] Script Date: 12/25/2008 16:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PartitionTab](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [varchar](50) NULL,
[Col2] [varchar](50) NULL,
[ModifiedDate] [date] NULL,
CONSTRAINT [PK_PartitionTab] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Lalu kita isi data pada table PartitionTab. Pada contoh di bawah saya mengisi 1000 data untuk setiap bulan dan mengisi data untuk tiga bulan
declare @nRowCount as int = 1
declare @FirstDay as date = '2009/01/01'
while @nRowCount <= 1000
begin
insert into PartitionTab (Col1,Col2,ModifiedDate) values ('Col1','Col2',DATEADD(DAY, @nRowCount % 30, @FirstDay))
set @nRowCount += 1
end
set @nRowCount = 1
set @FirstDay = '2009/02/01'
while @nRowCount <= 1000
begin
insert into PartitionTab (Col1,Col2,ModifiedDate) values ('Col1','Col2',DATEADD(DAY, @nRowCount % 28, @FirstDay))
set @nRowCount += 1
end
set @nRowCount = 1
set @FirstDay = '2009/03/01'
while @nRowCount <= 1000
begin
insert into PartitionTab (Col1,Col2,ModifiedDate) values ('Col1','Col2',DATEADD(DAY, @nRowCount % 30, @FirstDay))
set @nRowCount += 1
end
Sekarang, kita akan mulai mempartisi PartitionTab dengan menggunakan Create Partition Wizard. Pada object explorer, klik kanan PartitionTab, pilih Storage > Create Partition...
Klik Next pada dialog Welcome to the Create Partition Wizard
Pilih Modified Date sebagai Partitioning Column lalu klik Next pada dialog Select a Partition Column
Pada dialog Select a Partition Function, buat sebuah Partition Function dengan nama DatePartitionFunction lalu klik Next
Pada dialog Select a Partition Scheme tambahkan sebuah partition scheme dengan nama DatePartitionScheme lalu klik Next
Klik button Set boundaries..., lalu set Start date 1/1/2009, End date 3/30/2009, Date range Monthly lalu klik OK
Kemudian map setiap Boundary ke dalam FileGroup yang tadi kita buat lalu klik Next. Gunakan button Estimate Storage untuk menghitung jumlah space yang digunakan di setiap Filegroup.
Untuk langsung meng-apply Partition Table, pilih Run immediately pada dialog Select an Output Option lalu klik Next
Klik Finish pada dialog Review Summary
Partition Wizard akan melakukan partitioning pada table PartitionTab, klik Close untuk menutup Wizard
Untuk melihat jumlah data yang terdapat di setiap partisi, kita dapat menggunakan $Partition statement
USE PartitionDB
GO
SELECT $PARTITION.DatePartitionFunction(ModifiedDate) AS Partition,
COUNT(1) AS [COUNT] FROM dbo.PartitionTab
GROUP BY $PARTITION.DatePartitionFunction(ModifiedDate)
ORDER BY Partition ;
GO
Gunakan juga $Partition untuk melihat data yang terdapat pada satu partisi
SELECT ID, COl1, Col2, ModifiedDate FROM dbo.PartitionTab
WHERE $PARTITION.DatePartitionFunction(ModifiedDate) = 2
ORDER BY 1
