Partition Table Örneği

/*
2007 yılına ait verilerimizi tablomuzda 5 partiona böleceğiz. Partion Table için sırasıyla şunlar yapılır:

1. Partion Function oluşturulur,
2. Partion Scheme oluşturulur
3. File Gruplar oluşturulur.
   

*/

CREATE PARTITION FUNCTION [MonthlyConnPF] (datetime) AS RANGE RIGHT
FOR VALUES ('20070101', '20070401', '20070701', '20071001');

CREATE PARTITION SCHEME MonthlyConnPS AS PARTITION MonthlyConnPF TO (fg01, fg02, fg03, fg04, fg05);

CREATE TABLE [dbo].[ConnDetails]
(
ConnId int IDENTITY,
ConnClient varchar(100),
ConnUser varchar(100),
ConnLocation varchar(100),
ConnEndTime datetime,
ConnUsageInMins int
)
ON MonthlyConnPS(ConnEndTime)
GO

--Her bir file ı oluşturduğumuz filegrouplara ekliyoruz.

Alter Database test
Add File
(
Name = test01,
Filename = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test01.ndf',
Size = 1MB,
Filegrowth = 1MB
)
To FileGroup fg01

Alter Database test
Add File
(
Name = test02,
Filename = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test02.ndf',
Size = 1MB,
Filegrowth = 1MB
)
To FileGroup fg02

Alter Database test
Add File
(
Name = test03,
Filename = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test03.ndf',
Size = 1MB,
Filegrowth = 1MB
)
To FileGroup fg03

Alter Database test
Add File
(
Name = test04,
Filename = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test04.ndf',
Size = 1MB,
Filegrowth = 1MB
)
To FileGroup fg04

Alter Database test
Add File
(
Name = test05,
Filename = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test05.ndf',
Size = 1MB,
Filegrowth = 1MB
)
To FileGroup fg05

--Tablomuza rastgele 100.000 kayıt ekleyelim
insert into [ConnDetails]
(ConnClient, ConnUser, ConnLocation, ConnEndTime, ConnUsageInMins)
VALUES (mguzel, 'Istanbul', 'SD', dateadd(dd, convert(int, Rand()*365), '2006-1-1'), convert(int, Rand()*2e9))
go 100000

--2 nolu partionımızı sorgulayalım
Select * from dbo.conndetails Where $PARTITION.MonthlyConnPF(ConnEndTime) = 2 order by connendtime

Select * from Sys.Partitions where object_id = object_id('test.dbo.conndetails')

Select object_name(object_id), partition_number, rows from Sys.Partitions where object_id = object_id('test.dbo.ConnDetails')

Select $partition.MonthlyConnPF(ConnEndTime) As 'Partition Number', count(*) As 'Rows' From dbo.ConnDetails Group by $partition.MonthlyConnPF(ConnEndTime) Order by 'Partition Number'

Select *, $partition.MonthlyConnPF(ConnEndTime) AS 'Partition Number' From dbo.ConnDetails Order by 'Partition Number', ConnEndTime

Select * from sys.filegroups