Table Partitions

Table Partition is a new feature in SQL Server 2005.
Here I am posting a sample code how to create a Partitioned table and view its meta data in system tables.
/* We need to create a partition scheme and function on which we will partition the data */



create partition function part_test_func(datetime) as range
left for values ('2008-09-10','2008-09-20','2008-09-30')

/* so it has range values like
<= '2008-09-10' > '2008-09-10 and <= '2008-09-20' > '2008-09-20 and <= '2008-09-30' >'2008-09-30'
*/
/* Now we need to map file group to each of this partition
Here first , second and third are filegroups */


create partition scheme part_test_scheme
as partition part_test_func to(first,second,third,first)


/*Now we will cretae a table on this partition scheme */

create table part_test
(id int identity(1,1) ,
data varchar(100),
date datetime)
on part_test_scheme(date)



insert into part_test values('a','2008-09-05')
insert into part_test values('a','2008-09-15')
insert into part_test values('a','2008-09-25')
insert into part_test values('a','2008-10-05')



/* Now we will get info for partion function */
/* for partition function */
select * from sys.partition_functions where name = 'part_test_func'
/* for partition scheme */
select * from sys.partition_schemes where name = 'part_test_scheme'
or
select * from sys.partition_schemes where function_id in
(select function_id from sys.partition_functions where name = 'part_test_func')
/* To get rowinfo for partition wise */

select * from sys.partitions where object_id = object_id('part_test')

/*This will give data type info on which partition is created */

select * from sys.partition_parameters where function_id in
(select function_id from sys.partition_functions where name = 'part_test_func')

/* To get range for each partition */
select * from sys.partition_range_values where function_id in
(select function_id from sys.partition_functions where name = 'part_test_func')


select * from sys.indexes where object_id = object_id('part_test')

/* This will give info for each data space */
select * from sys.data_spaces

/* To get filegroup associated to each partition */

select p.*, d.name filegroupname from sys.destination_data_spaces p inner join sys.data_spaces d
on p.data_space_id = d.data_space_id
where partition_scheme_id in
(
select data_space_id from sys.partition_schemes where name = 'part_test_scheme'
)