Table Partitioning - Basics

Today I want to revisit on SQL Server Table partitioning basics. To get started

Step 1 - Refresh the SQL Server Notes on Partitioning by Jose Barreto

Step 2 - Horizontal Vs Vertical Partitioning (Reference - Link)
Hortizontal Partition - Partition based on certain column value. Requires Partition Scheme, Partition Function and Partition Tables. Table will have all the columns
Vertical Partitioning - Divide table into multiple tables with fewer columns

Step 3 - Experiment Hortizontal Partitioning
a. Partition Function - A partition function specifies how the table or index is partitioned
b. Partition Scheme - Defines File groups mapping
c. Creating Partitioned Table

Creating a Partitioned Table for Class (Partitioned based on Class 1-12). When we query on a class only the particular partition is queried.
--STEP 1
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'TestPartitionFunction')
DROP PARTITION FUNCTION TestPartitionFunction;
GO
create partition function TestPartitionFunction (int) as range for values (1,2,3,4,5,6,7,8,9,10);

--STEP 2
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'TestPartitionScheme')
DROP PARTITION SCHEME TestPartitionScheme;
GO
create partition scheme TestPartitionScheme as partition TestPartitionFunction all to ([PRIMARY]);
GO

--STEP 3
IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'CLASSTable')
DROP TABLE CLASSTable;
GO

--STEP 4
create table dbo.CLASSTable (Class int, Name VARCHAR(200), Studentid int) on TestPartitionScheme (Class);
GO
CREATE CLUSTERED INDEX CIX ON CLASSTable(Class) ON TestPartitionScheme(Class)
GO

--STEP 5 Insert Data
declare @i int, @j int, @k int;
set @i=1;
set @j=100;
set @k = 1;
while (1=1)
begin;
INSERT INTO CLASSTable (Class, Name, Studentid)
VALUES (@i, Convert(VARCHAR(100),@j)+'Name',@j)
set @j=@j+1;
set @k = @k+1;
if(@k>100)
begin
set @k = 0;
set @j = @j+100;
set @i = @i+1;
end
if(@i>10)
begin
break;
end
end;
go

--Data in Each Partition
SELECT $PARTITION.TestPartitionFunction(class) AS Partition, COUNT(*) AS [COUNT]
FROM dbo.CLASSTable
GROUP BY $PARTITION.TestPartitionFunction(class)
ORDER BY Partition ;

--Data Searched only in required partition
SELECT *,$partition.TestPartitionFunction(Class) AS 'Partition Detail'
FROM CLASSTable WHERE Studentid=108

--Verify
set statistics profile on;
SELECT * FROM dbo.CLASSTable WHERE Studentid = 108 and Class = 1

Related Reads - Enabling Partition Level Locking in SQL Server 2008
Using Filtered Statistics with Partitioned Tables.
Partitioned Tables and Indexes in SQL Server 2005
Partitioning Summary: SQL Server 2005 vs SQL Server 2008


Happy Learning!!!