SQL SERVER RULES

SQL SERVER RULES

Sunday, February 12, 2012

PARTITION THE TABLE IN SQL SERVER

Hello Guys,

Main advantage of sql server is to maintain the huge data records.

A single table can hold unlimited records with 1024 columns for a DBA its very hard to maintain large number of records in a single table.

solution is single table can be spitted up in different number of partitions to maintain the database very easily.

EXAMPLE FOR PARTITION THE TABLE

use tempdb

--create partition function

create partition function part_fun(int)
as range
for values(10,20,30,40,50)

--create the parttition schema

create partition scheme part_scheme
as partition part_fun
all to ('primary')

---create the table with some records

create table parttiton_table
(
col1 int,
col2 nvarchar(max)
)
on part_scheme(col1)

insert into parttiton_table
select 1,'john' union
select 2,'john' union
select 6,'john' union
select 11,'john' union
select 17,'john' union
select 21,'john' union
select 25,'john' union
select 28,'john' union
select 34,'john' union
select 38,'john' union
select 41,'john' union
select 44,'john' union
select 47,'john' union
select 56,'john' union
select 58,'john' union
select 61,'john' union
select 66,'john' union
select 71,'john' union
select 77,'john'

select * from parttiton_table

--retrive the partition data from the table

select $partition.part_fun(col1) as partition_data,col1 as data from parttiton_table
order by partition_data

No comments: