Partitioned Table
A partitioned table is a table in which the data is separated horizontally into multiple physical locations based on a range of values for a specific column. The physical locations for partitions are filegroups. For example, you could use a partitioned table to store sales orders and then separate the order records into different filegroups based on
the order date.
Benefits of Partitioned Tables
• The ability to implement separate backup strategies.
• Control over storage media.
• Index management benefits.
Partitioned Function
A partition function specifies the data type of the key used to partition the data and the
boundary values for each partition. The number of partitions defined by a partition
function is always one more than the number of boundary values that the function defines.
For example, a partition function that defines a Integer partitioning key with the
boundary values 1, 2, and 3’ will result in four partitions <1,1-2,2-3,>3.
Example:-
CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT
FOR VALUES ('01/01/2003', '01/01/2004', '01/01/2005')
Partitioned Function
A partition scheme maps the partitions that are defined in a partition function to the
filegroups where the partitions will be physically stored. You can map all partitions to the
same filegroup, or you can map some or all of the partitions to different filegroups,
depending on your needs.
• Index management benefits.
Partitioned Function
A partition function specifies the data type of the key used to partition the data and the
boundary values for each partition. The number of partitions defined by a partition
function is always one more than the number of boundary values that the function defines.
For example, a partition function that defines a Integer partitioning key with the
boundary values 1, 2, and 3’ will result in four partitions <1,1-2,2-3,>3.
Example:-
CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT
FOR VALUES ('01/01/2003', '01/01/2004', '01/01/2005')
Partitioned Function
A partition scheme maps the partitions that are defined in a partition function to the
filegroups where the partitions will be physically stored. You can map all partitions to the
same filegroup, or you can map some or all of the partitions to different filegroups,
depending on your needs.
Example:-
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg1, fg2, fg3, fg4, fg5)
---------------------------------------------------------------------------------
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
ALL TO ([PRIMARY])
Fg5 is next used partition will be used in future with partition table.
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg1, fg2, fg3, fg4, fg5)
---------------------------------------------------------------------------------
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
ALL TO ([PRIMARY])
Fg5 is next used partition will be used in future with partition table.
Example Partitioned Tables
CREATE TABLE TESTEMPLOYEE
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEY PRIMARY KEY (EMPID,RetMONTH))
ON MyPartitionScheme(RETMONTH)
Switching Partitions
You can swap a populated table or partition with an empty table or partition by using the
SWITCH clause of the ALTER TABLE statement. This technique is commonly used to
archive data from a partitioned table or to bulk-insert new data from a staging table.
ALTER TABLE dbo.PartitionedTransactions
SWITCH PARTITION 1
TO dbo.TransactionArchive
CREATE TABLE TESTEMPLOYEE
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEY PRIMARY KEY (EMPID,RetMONTH))
ON MyPartitionScheme(RETMONTH)
Switching Partitions
You can swap a populated table or partition with an empty table or partition by using the
SWITCH clause of the ALTER TABLE statement. This technique is commonly used to
archive data from a partitioned table or to bulk-insert new data from a staging table.
ALTER TABLE dbo.PartitionedTransactions
SWITCH PARTITION 1
TO dbo.TransactionArchive
Merging Partitions
You can use the ALTER PARTITION FUNCTION statement to merge a partition. When
you perform a merge operation, the partition for the boundary value specified in the
ALTER PARTITION FUNCTION statement is removed, and the data is merged into the
adjacent partition.
ALTER PARTITION FUNCTION pf_OrderDate()
MERGE RANGE ('01/01/2003')
You can use the ALTER PARTITION FUNCTION statement to merge a partition. When
you perform a merge operation, the partition for the boundary value specified in the
ALTER PARTITION FUNCTION statement is removed, and the data is merged into the
adjacent partition.
ALTER PARTITION FUNCTION pf_OrderDate()
MERGE RANGE ('01/01/2003')
Splitting Partitions
Like a merge, a split operation is performed by using the ALTER PARTITION
FUNCTION statement. This creates a new partition and reassigns the data accordingly.
The new partition is created on the filegroup designated as the next filegroup in each
partition scheme based on the partition function. If a partition scheme has no next
filegroup defined when the partition is split, an error occurs. So earlier fg5 next used partition is used by this function.
ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ('01/01/2006')
Designing Partitions Table To Archive Data In Another Database At The End Of Each Month Automatically.
Like a merge, a split operation is performed by using the ALTER PARTITION
FUNCTION statement. This creates a new partition and reassigns the data accordingly.
The new partition is created on the filegroup designated as the next filegroup in each
partition scheme based on the partition function. If a partition scheme has no next
filegroup defined when the partition is split, an error occurs. So earlier fg5 next used partition is used by this function.
ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ('01/01/2006')
Designing Partitions Table To Archive Data In Another Database At The End Of Each Month Automatically.
--Create Database for Partition table with 12 filegroups and Data file associated with each
CREATE DATABASE [PartitionDB] ON PRIMARY
( NAME = N'PartitionDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB.mdf' , SIZE = 4048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup1]
( NAME = N'PartitionDB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB1.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup10]
( NAME = N'PartitionDB10', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB10.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup11]
( NAME = N'PartitionDB11', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB11.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup12]
( NAME = N'PartitionDB12', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB12.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup2]
( NAME = N'PartitionDB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup3]
( NAME = N'PartitionDB3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup4]
( NAME = N'PartitionDB4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB4.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup5]
( NAME = N'PartitionDB5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB5.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup6]
( NAME = N'PartitionDB6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB6.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup7]
( NAME = N'PartitionDB7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB7.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup8]
( NAME = N'PartitionDB8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB8.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup9]
( NAME = N'PartitionDB9', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB9.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PartitionDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Sample Picture is Given
( NAME = N'PartitionDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB.mdf' , SIZE = 4048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup1]
( NAME = N'PartitionDB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB1.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup10]
( NAME = N'PartitionDB10', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB10.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup11]
( NAME = N'PartitionDB11', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB11.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup12]
( NAME = N'PartitionDB12', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB12.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup2]
( NAME = N'PartitionDB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup3]
( NAME = N'PartitionDB3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup4]
( NAME = N'PartitionDB4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB4.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup5]
( NAME = N'PartitionDB5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB5.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup6]
( NAME = N'PartitionDB6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB6.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup7]
( NAME = N'PartitionDB7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB7.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup8]
( NAME = N'PartitionDB8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB8.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup9]
( NAME = N'PartitionDB9', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB9.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PartitionDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Sample Picture is Given
--Use Database
use PartitionDB
go
--create partition function to create logical boundary for each month
CREATE PARTITION FUNCTION MyPartitionRange (TINYINT)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11)
GO
--create partition scheme to associate in logical boundary with file groups
CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartitionRange
To([filegroup1],[filegroup2],[filegroup3],[filegroup4],[filegroup5],[filegroup6],[filegroup7],[filegroup8],[filegroup9],[filegroup10],[filegroup11],[filegroup12])
GO
--now create a table with store data using partition scheme and partition function
--see we have created computed column RETMONTH to idetified the month of employee retirment and pass this to partition scheme. make sure if you are associate a computed column with partition schema it must be persisted.
CREATE TABLE EMPLOYEE
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEY PRIMARY KEY (EMPID,RetMONTH))
ON MyPartitionScheme(RETMONTH)
Go
--populate partition table with data.(adventure works sample database is reuired for [Person].[Contact] table.
INSERT INTO EMPLOYEE
SELECT [FirstName]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[Contact]
go
--varify no of records in each partition by using sys.partition function
SELECT partition_number,rows
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('EMPLOYEE')
go
--varify rows are related to which partition for this we are usinf $partition predicate with partition function
SELECT *,$PARTITION.MyPartitionRange(retmonth)as PartitionNo FROM EMPLOYEE
go
--ARCHIVED Destination
--creating Database for archived table
Create database ArchivedDB
Go
Use ArchivedDB
go
-- Create table to store Archived data.
CREATE TABLE ArchivedData
(EMPID INT ,EMPNAME CHAR(40),RETDATE DATETIME )
Go
--varify there is no records in ArchivedData
SELECT * FROM ArchivedData
go
--we are creating temprory table to switch data using StoreProc to transfer data from source to temprory table and then archicedtable
--Considrations
--1. Switch can transfer data to empty table or empty partition only.-
--2. Destination(Empty Table or Partition) must be exits on same filegroup from where data is extracted.
--3.you can not create a temp table becouse it exists in TEMDB.
--4.Temp table must have same schema as source table even charter length of columns.
-- we are using dynamic sql to craete temp table on same filegroup from data is extracted by passing filegroup as parameter.
GO
USE PartitionDB
GO
CREATE PROC SWITCHTABLE @FG CHAR(20)
AS
BEGIN
exec ('CREATE TABLE TempEmployee
use PartitionDB
go
--create partition function to create logical boundary for each month
CREATE PARTITION FUNCTION MyPartitionRange (TINYINT)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11)
GO
--create partition scheme to associate in logical boundary with file groups
CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartitionRange
To([filegroup1],[filegroup2],[filegroup3],[filegroup4],[filegroup5],[filegroup6],[filegroup7],[filegroup8],[filegroup9],[filegroup10],[filegroup11],[filegroup12])
GO
--now create a table with store data using partition scheme and partition function
--see we have created computed column RETMONTH to idetified the month of employee retirment and pass this to partition scheme. make sure if you are associate a computed column with partition schema it must be persisted.
CREATE TABLE EMPLOYEE
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEY PRIMARY KEY (EMPID,RetMONTH))
ON MyPartitionScheme(RETMONTH)
Go
--populate partition table with data.(adventure works sample database is reuired for [Person].[Contact] table.
INSERT INTO EMPLOYEE
SELECT [FirstName]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[Contact]
go
--varify no of records in each partition by using sys.partition function
SELECT partition_number,rows
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('EMPLOYEE')
go
--varify rows are related to which partition for this we are usinf $partition predicate with partition function
SELECT *,$PARTITION.MyPartitionRange(retmonth)as PartitionNo FROM EMPLOYEE
go
--ARCHIVED Destination
--creating Database for archived table
Create database ArchivedDB
Go
Use ArchivedDB
go
-- Create table to store Archived data.
CREATE TABLE ArchivedData
(EMPID INT ,EMPNAME CHAR(40),RETDATE DATETIME )
Go
--varify there is no records in ArchivedData
SELECT * FROM ArchivedData
go
--we are creating temprory table to switch data using StoreProc to transfer data from source to temprory table and then archicedtable
--Considrations
--1. Switch can transfer data to empty table or empty partition only.-
--2. Destination(Empty Table or Partition) must be exits on same filegroup from where data is extracted.
--3.you can not create a temp table becouse it exists in TEMDB.
--4.Temp table must have same schema as source table even charter length of columns.
-- we are using dynamic sql to craete temp table on same filegroup from data is extracted by passing filegroup as parameter.
GO
USE PartitionDB
GO
CREATE PROC SWITCHTABLE @FG CHAR(20)
AS
BEGIN
exec ('CREATE TABLE TempEmployee
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEYTEMP PRIMARY KEY (EMPID,RETMONTH))
ON '+@FG)
END
Go
-- Creating another storeproc to swith data from source to temp table created earlier we have to pass partition id from data gets extracted passing partition id as parameter.
create proc SWAPPARTITION @ID INT
AS
BEGIN
EXEC ('ALTER TABLE EMPLOYEE switch PARTITION
'+' '+@ID+' TO TempEmployee')
END
go
--another proc to transfer data from temp table to destination archived table in archivedDB
CREATE PROC ARCHIVEDATA
AS
BEGIN
INSERT INTO ArchivedDB.dbo.ArchivedData SELECT EMPID,EMPNAME,RETDATE FROM TempEmployee
END
go
-- create single store proc to call all three proc(creaete table,swith partionand transfer data)
--ones you get partitionid you can extract the file group associtead with it so we are using a query to know archived partition file group and pass this to first procedure to create temp table after archiving we have to make sure that temp table must be drop for next time.
CREATE PROC SWAPEMPLOYEE @ID INT
AS
BEGIN
Declare @fg Char(20)
select @fg=fg.Name from sys.indexes i inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
left outer join sys.partition_range_values prv on prv.boundary_id = dds.destination_id
where i.[object_id] = object_id('Employee') and destination_id =@id
EXEC SWITCHTABLE @FG
EXEC SWAPPARTITION @ID
EXEC ARCHIVEDATA
DROP TABLE TempEmployee
END
Go
CONSTRAINT PKKEYTEMP PRIMARY KEY (EMPID,RETMONTH))
ON '+@FG)
END
Go
-- Creating another storeproc to swith data from source to temp table created earlier we have to pass partition id from data gets extracted passing partition id as parameter.
create proc SWAPPARTITION @ID INT
AS
BEGIN
EXEC ('ALTER TABLE EMPLOYEE switch PARTITION
'+' '+@ID+' TO TempEmployee')
END
go
--another proc to transfer data from temp table to destination archived table in archivedDB
CREATE PROC ARCHIVEDATA
AS
BEGIN
INSERT INTO ArchivedDB.dbo.ArchivedData SELECT EMPID,EMPNAME,RETDATE FROM TempEmployee
END
go
-- create single store proc to call all three proc(creaete table,swith partionand transfer data)
--ones you get partitionid you can extract the file group associtead with it so we are using a query to know archived partition file group and pass this to first procedure to create temp table after archiving we have to make sure that temp table must be drop for next time.
CREATE PROC SWAPEMPLOYEE @ID INT
AS
BEGIN
Declare @fg Char(20)
select @fg=fg.Name from sys.indexes i inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
left outer join sys.partition_range_values prv on prv.boundary_id = dds.destination_id
where i.[object_id] = object_id('Employee') and destination_id =@id
EXEC SWITCHTABLE @FG
EXEC SWAPPARTITION @ID
EXEC ARCHIVEDATA
DROP TABLE TempEmployee
END
Go
--create a t-sql job and schedule at the end on each month with given code.
go
use PartitionDB
go
DECLARE @PNO INT
SET @PNO=DATEPART(MM,GETDATE())
EXEC SWAPEMPLOYEE @PNO
go
go
use PartitionDB
go
DECLARE @PNO INT
SET @PNO=DATEPART(MM,GETDATE())
EXEC SWAPEMPLOYEE @PNO
go
2. give name to job and select step tab.
3. click new step.
4. fill step page as shown in picture.
5.create schedule for job occurred at end of each month. Fill as given in picture.
6. change the system date to end of month set timing 11:57 pm and wait for two minutes. --check previous month data in employee table
go
SELECT * FROM EMPLOYEE WHERE RETMONTH=datepart(mm,getdate())
7 --now check data in archived table
go
select * from ArchivedDB.dbo.ArchivedData
--clean your system
go
use master
go
drop database ArchivedDB,PartitionDB
No comments:
Post a Comment