CREATE PARTITION FUNCTION [Orders__Function](datetime) AS RANGE LEFT FOR VALUES

(N'2012-12-31 23:59:59.997', N'2013-03-31 23:59:59.997', N'2013-06-30 23:59:59.997')

Partition

Partition 1

Partition 2

Partition 3

Partition 4

Data Range

<= 2012-12-31

<= 2013-03-31

<= 2013-06-30

> 2013-06-30

/*****************************************************

Step 1: Scenario SETUP

*************************************************/

-- Add Filegroups

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG1];

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG2];

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG3];

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG4];

GO

Alter database [SW_TEST] set recovery simple

go

-- Add Files

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'C:\SQL_DATA\PartitionTest_1.ndf') TO FILEGROUP [FG1]

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_2', FILENAME = N'C:\SQL_DATA\PartitionTest_2.ndf') TO FILEGROUP [FG2]

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_3', FILENAME = N'C:\SQL_DATA\PartitionTest_3.ndf') TO FILEGROUP [FG3]

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_4', FILENAME = N'C:\SQL_DATA\PartitionTest_4.ndf') TO FILEGROUP [FG4]

GO

-- Create partition function

CREATE PARTITION FUNCTION [Orders__Function](datetime) AS RANGE LEFT FOR VALUES

(N'2012-12-31 23:59:59.997', N'2013-03-31 23:59:59.997', N'2013-06-30 23:59:59.997')

go

-- Create partition Scheme

CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders__Function] TO

([FG1],[FG2],[FG3],[FG4])

-- Create table

CREATE TABLE [dbo].[Orders](

[OrdDate] [datetime] NOT NULL,

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Addr] varchar(100) NOT NULL)

-- Partition the table

CREATE UNIQUE CLUSTERED INDEX IX_Orders

ON [Orders](OrdDate asc,ID asc)

ON [Orders__Scheme] (OrdDate);

GO

-- Insert rows into partitions (partition 4 in this case)

declare @i int

set @i = 1

declare @date Datetime

while (@i < 1000)

begin

set @date = dateadd(mi,@i,'2012-11-01 10:17:01.000')

--insert into testtable values (@date)

insert into [Orders] values (@date, 'Denzil')

insert into [Orders] values (dateadd(month,3,@date), 'Denzil')

insert into [Orders] values (dateadd(month,6,@date), 'Denzil')

insert into [Orders] values (dateadd(month,9,@date), 'Denzil')

set @i = @i+1;

end

-- Check the rowcount in each partition

select $PARTITION.[Orders__Function](Orddate) as PartionNum,COUNT(*) as CountRows from Orders

Group by $PARTITION.[Orders__Function](Orddate)

SELECT

    t.name as TableName,i.name as IndexName,

    p.partition_id as partitionID,

    p.partition_number,rows, fg.name

FROM sys.tables AS t

    INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)

INNER JOIN sys.partitions AS p ON (t.object_id = p.object_id and i.index_id = p.index_id)

INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)

INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)

WHERE (t.name = 'Orders') and (i.index_id IN (0,1))

--- We now want to SPLIT a non-empty partition, so preparing for that

-- Add new Filegroup and file

ALTER DATABASE SW_TEST ADD FILEGROUP [FG5];

ALTER DATABASE SW_TEST ADD FILE

( NAME = N'PartitionTest_5', FILENAME = N'C:\SQL_DATA\PartitionTest_5.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

TO FILEGROUP [FG5]

GO

-- Set the next used partition

Alter partition scheme [Orders__Scheme] NEXT USED [FG5]

--- Traditional Split

-- Clear Records to demonstrate Log records generated

checkpoint

go

-- Select to demonstrate that there are no log records for that table

select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)

where AllocUnitName= 'dbo.Orders.IX_Orders'

group by Operation

order by count(*) desc

-- Split the non-empty partition

ALTER PARTITION FUNCTION Orders__Function() SPLIT RANGE ('2013-09-30 23:59:59.99')

-- Show how many log records generated, there is data movement Deletes followed by inserts

select Operation,AllocUnitName,count(*) as NumLogRecords from fn_dblog(NULL,NULL)

where AllocUnitName= 'dbo.Orders.IX_Orders'

group by Operation,AllocUnitName

order by count(*) desc

SELECT

    t.name as TableName,i.name as IndexName,

    p.partition_id as partitionID,

    p.partition_number,rows, fg.name

FROM sys.tables AS t

    INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)

INNER JOIN sys.partitions AS p ON (t.object_id = p.object_id and i.index_id = p.index_id)

INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)

INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)

WHERE (t.name = 'Orders') and (i.index_id IN (0,1))