Partitioning is a feature is introduced to aid in
maintenance on larger tables and to present fast ways to load and remove large
amounts of data from a table. Partitioning can boost query performance.
Each table has a default partition which we call it
PRIMARY partition which contain all data. To add partitions, you need to define
a partition function and the corresponding partition scheme. A partition scheme
uses the partition function to map data ranges to appropriate file groups. If
the partition function defines three ranges, then partition scheme must map
them to four file groups. Three ranges defined by the partition function and
the forth, catchall range, for all data outside the boundaries of the
predefined ranges.
Lets take an example to understand the partition. Here
we are creating a table with three field id, name and salary. Later we will
base of partition on ID field.
create table tab_partitionDemo
(id int identity(1,1),
name varchar(150),
salary int)
Command(s)
completed successfully.
Lets insert 1000 dumy records in newly created table.
declare
@rwCnt int =
10000
while
@rwCnt >= 1
begin
insert into
tab_partitionDemo values('abc' +
cast(@rwCnt as varchar), 10*rwCnt)
set @rwCnt = @rwcnt -1
end
Now we have our table ready, let add two file group. A
filegroup is a logical storage unit. Every database has a primary filegroup
that contains the primary data file (.mdf). An additional, user-defined,
filegrups can be created to contain secondary files (.ndf). We will create two
file group here, one which will store first 5,000 records and the other one
will store data from 5,000 to 10,000.
ALTER DATABASE TestDB ADD
FILEGROUP partitionDemoFG01_50K
GO
ALTER DATABASE TestDB ADD
FILEGROUP partitionDemoFG50K_10K
GO
Command(s)
completed successfully.
Now its turn to add file(.ndf) to the newly created
filegroup, which will hold data.
ALTER DATABASE TestDB
ADD FILE
(NAME
= N'data_01_10',
FILENAME = N'D:\SQL
Server\Data\data_FilePartitionDemoF01_50K.ndf',
SIZE = 5000KB,
MAXSIZE =
10000MB,
FILEGROWTH =
500MB)
TO FILEGROUP
partitionDemoFG01_50K
GO
ALTER DATABASE TestDB
ADD FILE
(NAME
= N'data_11_20',
FILENAME = N'D:\SQL
Server\Data\data_partitionDemoF50K_10K.ndf',
SIZE = 5000KB,
MAXSIZE =
10000MB,
FILEGROWTH =
500MB)
TO FILEGROUP
partitionDemoFG50K_10K
GO
Command(s)
completed successfully.
To check the file created we can query
sys.database_files
SELECT name as [FileName],
physical_name as [FilePath]
FROM sys.database_files where
type_desc = 'ROWS'
GO
Now its time to create partition function, a partition function
is one which maps the table data to the partition based upon the partition
column. Here in below code we are creating 3 partition on the bases of int
value field i.e ID.
CREATE PARTITION FUNCTION
IDPartitionFunction (int)
AS
RANGE LEFT FOR VALUES (5000,10000 );
Command(s)
completed successfully.
Now we need to map the partition to each partition
group with the help of Partition Schema.
CREATE PARTITION SCHEME IDPartitionSchema
AS PARTITION IDPartitionFunction
TO (partitionDemoFG01_50K,partitionDemoFG50K_10K,[PRIMARY]);
Command(s)
completed successfully.
Now we need to redistribute the table data to various
partition, for that we need to either create clustered index on selective table
column or if it already present then drop and recreate with On clause on
Partition column.
create clustered index
ix_tab_partitionDemo_id
on dbo.tab_partitionDemo(ID)
ON
IDPartitionSchema(id)
Command(s)
completed successfully.
This is the only thing one need to do for partition,
lets check whether our data get distributed to various partition by querying sys.index
table.
SELECT o.name objectname,i.name indexname,
partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%tab_partitionDemo%'
objectname indexname partition_id partition_number rows
tab_partitionDemo
ix_tab_partitionDemo_id 72057594148093952 1 5000
tab_partitionDemo
ix_tab_partitionDemo_id 72057594148159488 2 5000
tab_partitionDemo
ix_tab_partitionDemo_id 72057594148225024 3 0
Here we saw 10K records of the table get distributed to
two partition. Now lets add one more records with partition column value more
then 10k.
insert into dbo.tab_partitionDemo
values('Rakesh',40000)
SELECT o.name objectname,i.name indexname,
partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%tab_partitionDemo%'
objectname indexname partition_id partition_number rows
tab_partitionDemo
ix_tab_partitionDemo_id 72057594148093952 1 5000
tab_partitionDemo
ix_tab_partitionDemo_id 72057594148159488 2 5000
tab_partitionDemo
ix_tab_partitionDemo_id 72057594148225024 3 1
So we can see the newly created row go to primary partition.
That’s what we were expecting. Cool!!!
SQL Server, where there is mountain of
happiness sits :)
Post Reference: Vikram
Aristocratic Elfin Share
