USE master
GO

--> Create database with 11 filegroups (10 of them to be used in partitioning)
create database PartitionTest
on primary (name = primary_data, filename = 'D:\MSSQL\PartitionTest.mdf', size = 10MB, filegrowth = 10MB),
filegroup fg001 (name = fg001_data, filename = 'D:\MSSQL\PartitionTest_fg001.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg002 (name = fg002_data, filename = 'D:\MSSQL\PartitionTest_fg002.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg003 (name = fg003_data, filename = 'D:\MSSQL\PartitionTest_fg003.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg004 (name = fg004_data, filename = 'D:\MSSQL\PartitionTest_fg004.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg005 (name = fg005_data, filename = 'D:\MSSQL\PartitionTest_fg005.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg006 (name = fg006_data, filename = 'D:\MSSQL\PartitionTest_fg006.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg007 (name = fg007_data, filename = 'D:\MSSQL\PartitionTest_fg007.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg008 (name = fg008_data, filename = 'D:\MSSQL\PartitionTest_fg008.ndf', size = 10MB, filegrowth = 10MB),
filegroup fg009 (name = fg009_data, filename = 'D:\MSSQL\PartitionTest_fg009.ndf', size = 10MB, filegrowth = 10MB),
filegroup fgall (name = fgall_data, filename = 'D:\MSSQL\PartitionTest_fgall.ndf', size = 10MB, filegrowth = 10MB)
log on (name = db_log, filename = 'D:\MSSQL\PartitionTest_log.ldf', size = 10MB, filegrowth = 10MB)
go

use PartitionTest
go

--> Create partition function (must have same number of ranges as numbers of partitions)
create partition function part_func_calls (int)
as range left for values (
	1000,
	2000,
	3000,
	4000,
	5000,
	6000,
	7000,
	8000,
	9000)
go

--> Create partition scheme mapping between the partitioning function and the filegroups (must be a 1 to 1 mapping)
create partition scheme part_sch_calls 
as partition part_func_calls to (fg001, fg002, fg003, fg004, fg005, fg006, fg007, fg008, fg009, fgall)
go

--> Create partitioned table
create table calls (
	call_id int identity(1, 1) primary key clustered,
	a_number bigint,
	b_number bigint,
	start_time datetime,
	duration int)
 on part_sch_calls (call_id)
 go
 
--> Function to generate random numbers, used to create sample data
create function randomnumber (@min bigint, @max bigint, @rand float)
returns bigint
as
begin
	return CAST(((@Max + 1) - @Min) * @Rand + @Min AS bigint)
end
go

set nocount on
go

--> Generate 11k rows of sample data 
--> NOTICE: the generated data will create 2000 rows in the last partition on purpose!
declare 
	@end int = 11000,
	@current int = 1
	
while (@current <= @end)
	begin
		insert into calls (a_number, b_number, start_time, duration)
		select 
			dbo.randomnumber (22000000, 22999999, RAND()),
			dbo.randomnumber (22000000, 22999999, RAND()),
			DATEADD(ss, -1 * dbo.randomnumber (36000, 100000000, RAND()), getdate()),
			dbo.randomnumber (3, 3600, RAND())
			
		select @current = @current + 1
	end
go

--> Create a partitioned nonclustered index on te same partitioning schema as the table itself 
create nonclustered index ix_start_time on calls(start_time) on part_sch_calls(call_id)
go

--> Add new filegroup to be able to add a new partition
alter database PartitionTest add filegroup fg010 
go

--> Add new file for the partition and add it to the new filegroup
alter database PartitionTest 
	add file (name = fg010_data, filename = 'D:\MSSQL\PartitionTest_fg11.ndf', size = 10MB, filegrowth = 10MB)
		to filegroup fg010 
go

--> Add the new filegroup to the partition scheme
alter partition scheme part_sch_calls next used fg010
go

--> Add the new partition
--> NOTICE: this will split the last partition that has 2000 rows in to two partitions with 1000 each
--  to fix the "logical error" with the overflowing data. This will also move 1000 rows of data from fgall
--  to fg010 so it will take some time and cause quite a bit of IO
alter partition function part_func_calls() split range (10000)
go