Table Partitioning Basic Implementation in sql server 2014 : Demo

I have tried to implement Partitioning in SQL server 2014 , to see if some thing is modified since SQl 2005. But same code is good to go with 2014 .

Note from Microsoft: "SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data. Partitioned tables and indexes are not available in every edition of Microsoft SQL Server"

Here's basic implementation of partitioning in SQL 2014 for start-ups.

Quick Download for scripts

/* Create New Test Database with two different file groups*/
USE Master
GO
Drop database Partitioning
CREATE DATABASE Partitioning
ON PRIMARY
(NAME='PartitioningDB_Part1', FILENAME= 'C:\Data\PartitioningDB_Part1.mdf'),
FILEGROUP PartitioningDB_Part2
(NAME = 'PartitioningDB_Part2', FILENAME ='C:\Data\PartitioningDB_Part2.ndf' );
GO

/*Create Partition Range Function*/
USE Partitioning
GO
CREATE PARTITION FUNCTION Partitioning_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (5000);
GO

/* Attach Partition Scheme to File Groups*/
CREATE PARTITION SCHEME Partitioning_PartitionScheme
AS PARTITION Partitioning_PartitionRange
TO ([PRIMARY], PartitioningDB_Part2 /*this is file group name*/);
GO

/* Create Table with Partition Key and Partition Scheme */
CREATE TABLE Partition_Table 
(ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
ON Partitioning_PartitionScheme (ID);
GO

/* Create Index on Partitioned Table ---> (Optional but Recommended)  */
CREATE  CLUSTERED INDEX PK_Partition_Table
ON Partition_Table(ID)
ON Partitioning_PartitionScheme (ID);
GO

/* Insert Data in Partitioned Table */
INSERT INTO Partition_Table (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'New Delhi'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'Mumbai'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'Benglaru'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Kolkata'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

/*  Verify Rows Inserted in Partitions */
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='Partition_Table';
GO