SQL Server partitioning (IV) — An ideal example of partitioning

USE [master]
GO
if exists (select * from sys.databases where name = 'Test_1')
drop database Test_1
GO
--create a new db with two file groups "Test_A" and "Test_B"

CREATE DATABASE [Test_1] ON  PRIMARY 
( NAME = N'test_1', FILENAME = N'D:\sqldata\test_1.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [test_A] 
( NAME = N'Test_A', FILENAME = N'D:\sqldata\test_A.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [test_B] 
( NAME = N'Test_B', FILENAME = N'D:\sqldata\test_B.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'D:\sqldata\Test_log.ldf' , SIZE = 7616KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

USE [Test_1]
GO

--if partition function exist needs to drop it first
IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'test_partition')
DROP PARTITION FUNCTION [test_partition]
GO

--create partition function for the schema, 
--partition function is set a range to ensure the row with what field value should be in which partition ---create a simple partition function, 
--this function using 1000 as the range border and split to two area

create partition function test_partition(int)
AS
RANGE LEFT FOR VALUES (1000) 
go

--create schema, schema needs to set a partition function 
--and point the partition distribution on the existing file groups
IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'test_scheme')
DROP PARTITION SCHEME test_scheme
GO

Create partition scheme test_scheme 
AS 
PARTITION [test_partition] TO (test_A,test_B)
GO

--create table with partition
if object_id('student','U') is not null
drop table student;
go

create table student
(
    id int identity(1,1) not null,
    name varchar(10) not null,
    class int not null,
    grade int
) on test_scheme(class) --point the schema and partition based on which column
go

--insert some test data

-- this row should reside on filegroup A
insert into student values ('AQU',10,100);

--this row should reside on filegroup A as the option is "LEFT" 
insert into student values ('AQU_边界',1000,89); 

--this row should reside on filegroup B
insert into student values ('BQU',1001,90); 

go

--Check the result, $partition.partition function(partition column) 
--is able to return row partition number*/

select *, $partition.test_partition(class) from student as PartitionNumber
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s