--------------------------------------------------------------------------------------------------------------
~
i. A table is created on a Schema.
ii. Files where the data will be stored that belongs to a Group
iii.To the Schema the parition function created is assigned.
iv.Create table - the empid is passed.
~
Database properties:
File: .mdf,ldf
File Group:
example:
i. For a table empid column has values 1 - 5000
a. Requirement to store empid: 1 - 999 ( in Partition1)
1000 - 1999 ( in Partition2)
Putting the data into buckets makes data search faster.
To do that:
1. Define a Partition Function ( specify the range of data to store)
2. Define a Partition Schema ( on which the table can be created)
a.Attach the Partition Function to the Schema
b.In the Schema the File Groups are mentioned ( those are created first in Database before running the Schema statement)
c.In the Database properties, create files and specify the filegroup of it.
d.Create the table ( on the Schema) - pass the empid column
--------------------------------------------------------------------------------------------------------------
create partition function
UI:
Create file groups in DB properties
Create file in DB properties ( associate with the above file groups)
create partition scheme
create table & associate with the scheme.
populate table data
select $partition - partition function created earlier
alternate: UI you can right click on Table - choose "Storage" and choose create new partition and define the function & scheme and its done.
There is Manage partition , the same allows to modify the existing partitions.
Syntax:
create partition function cust_part_func(int) as range right for values(1000, 2000,3000,4000,5000)
create partition scheme cust_part_scheme as partition cust_part_func to (fgp1,fgp2,fgp3,fgp4,fgp5,fgp6)
Sql Server Management Studio: Database name ("PartitionDB") , properties, choose "filegroups" and just enter the above names. then choose "file" (say fg1)create it and associate with the filegroups just created( above fgp1).
create table partition (EMPID int identity(1,1) not null, empdate datetime null) on cust_part_scheme (empid);
declare @i int
set @i = 0
while @i< 10000
Begin
insert into [partition] (empdata) values (GETDATE());
set @i = @i +1
end
select $partition cust_part_func(EMPID) as 'partition number',* from [partition]
-- it will show until 999 the partition 1, then show the partition 2
Table make into ranges, into different buckets. Separate File Groups -so query faster.
Sql Server Management Studio:
1.right click on Database,choose properties,"File" one can see the .mdf and .ldf file defn.
Partitioning just having the .mdf file in different file groups.
(when file group defined it shows .ndf files too)
2.steps to create partition.
a.Define a partition function ( specify how we are going to divide the data.)
b.Define a schema ( associate the partition function to this schema)
c.Attach a table to it.
i.Create a Database by right click on Database and new Database named [ PartitionDB ].
ii.In the new Database "PartitionDB" right click and choose "New Query".
iii.create partition function cust_part_func(int) as range right for values(1000, 2000,3000,4000,5000)
create partition scheme cust_part_scheme as partition cust_part_func to (fgp1,fgp2,fgp3,fgp4,fgp5,fgp6)
--fgp.. are file groups (yet to be created).[note:Run after the below step]
iv.right click on "PartitionDB" , properties, choose "filegroups" and just enter the above names.
then choose "file" (say fg1)create it and associate with the filegroups just created( above fgp1).
iv. create table & associate with the scheme.
create table partition (EMPID int identity(1,1) not null, empdate datetime null) on cust_part_scheme (empid);
v.populate data.
declare @i int
set @i = 0
while @i< 10000
Begin
insert into [partition] (empdata) values (GETDATE());
set @i = @i +1
end
select $partition cust_part_func(EMPID) as 'partition number',* from [partition]
-- it will show until 999 the partition 1, then show the partition 2
