How to Partition an existing SQL Server Table
Simply
in Three Steps :
1. Create Range
Function – Here example on – Date Column
2. Create Parition
Scheme to bind Date Range Function to FILE GROUPS.
3. Create Clustered
Index on date column on Table (partition)
Check
the Partion Table Now !.
STEP 0:
-- Create Table " Appleip "
Create
Table Appleip (SNO int, Name varchar(50), SVR datetime)
-- Insert Data into table, example : 1000 rows
DECLARE
@val INT
Select
@val = 1
WHILE
@val < 1000
BEGIN
INSERT
INTO Appleip(SNO, Name, SVR)
select
@val , 'SVR'+ CONVERT(char(1),@val), (GETDATE() - @val)
SET
@val = @val + 1
END
Select
* from
Appleip
--SCREEN
SHOT1
-- Befor partition check for partition on Table “Appleip“
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 '%Appleip%'
--SCREEN
SHOT2
n
P A R T I T I O N I N G
n SQL
Server Partitioned Table Creation
STEP 1:
-- Function for Partition
CREATE
PARTITION FUNCTION
myDateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('20110101', '20120101','20130101')
GO
STEP 2:
-- Scheme for Partition to bind function & filegroups.
CREATE
PARTITION SCHEME myPartitionScheme
AS PARTITION myDateRangePF ALL
TO ([PRIMARY])
GO
--Fnction & Schema linked info
SELECT
ps.name,pf.name,boundary_id,value
FROM
sys.partition_schemes ps
INNER
JOIN sys.partition_functions
pf ON pf.function_id=ps.function_id
INNER
JOIN sys.partition_range_values
prf ON pf.function_id=prf.function_id
STEP 3:
-- CREATE CLUSTERED INDEX on datecolumn field to build partition
CREATE
CLUSTERED INDEX
IX_TABLE1_partitioncol ON dbo.Appleip (SVR)
WITH (STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON myPartitionScheme(SVR)
GO
-- After partition check for partitions ON Table “Appleip”
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 '%Appleip%'
--SCREEN
SHOT3
***
SUCCESSFULL!
Happy Learning !
Raj
