2007 yılına ait verilerimizi tablomuzda 5 partiona böleceğiz. Partion Table için sırasıyla şunlar yapılır:
- Partion Function oluşturulur,
- Partion Scheme oluşturulur
- 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
1 yorum:
Teşekkürler.
Yorum Gönder