Create Partition in SQL Server 2008 R2 or 2008...

Creating partition for SQL Server 2008 R2 or 2008 tables with Huge Volumes

Step 1: Create a new Database called PartitionDaniel using the SQL Script as given below. Before creating the Database, check whether the Database already exists. If the Database exists alter the Database to Single Mode User, since some other user might be using the Database and then drop the Database
IF db_ID('PartitionDaniel') IS NOT NULL

BEGIN

                USE Master;

                ALTER DATABASE [PartitionDaniel] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

                DROP DATABASE [PartitionDaniel];

END

GO

 

Once already existing Database is Dropped, recreate the database and apply the File size and Log Size using the Create Database SQL Script as shown below

 

CREATE DATABASE [PartitionDaniel]

GO

 

ALTER DATABASE [PartitionDaniel] MODIFY FILE ( NAME = N'PartitionDaniel', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB ); 

ALTER DATABASE [PartitionDaniel]  MODIFY FILE ( NAME = N'PartitionDanielLOG', SIZE = 128MB , FILEGROWTH = 128MB );

GO

 

USE PartitionDaniel;

GO

 

Now the Database is ready to create the Partition. I had taken few reference from "Kendra the Little" who is big @ Heart explaining these Complex Concept into Simpler one :-)

 

Step 2: Creating Helper Objects

 

Create a schema called Partition Helper - PH and a View called File Group Details which allows us to see the entire details of the Each and every File Group created by the partitions

 

CREATE SCHEMA [PH] AUTHORIZATION dbo;

GO

 

CREATE VIEW ph.FileGroupDetail

AS

SELECT 

        pf.name 'Partition Name',

        ps.name 'Partition Scheme Name' ,

        p.partition_number  'Partition #',

        ds.name 'Partition File Group Name' ,

        pf.type_desc  'Partition File Type Description' ,

        pf.fanout AS 'Partition File Fanout',

        pf.boundary_value_on_right  'Boundary Value On Right',

        OBJECT_NAME(si.object_id) 'Object Name',

        rv.value AS 'Range Value',

        SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows ELSE 0 END) AS 'Number of Rows' ,

        SUM(dbps.reserved_page_count) * 8 / 1024. AS 'Reserved MB All Indexes' ,

        SUM(CASE ISNULL(si.index_id, 0) WHEN 0 THEN 0 ELSE 1 END) AS 'Number of Indexes'

FROM   

        sys.destination_data_spaces AS dds

        JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id

        JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id

        JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id

        LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id AND dds.destination_id =

        CASE pf.boundary_value_on_right WHEN 0 THEN rv.boundary_id ELSE rv.boundary_id + 1 END

        LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id

        LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id AND si.index_id = p.index_id AND

        dds.destination_id =  p.partition_number

        LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id AND p.partition_id = dbps.partition_id

GROUP BY ds.name ,

        p.partition_number ,

        pf.name ,

        pf.type_desc ,

        pf.fanout ,

        pf.boundary_value_on_right ,

        ps.name ,

        si.object_id ,

        rv.value;

GO

 

Create One more View called Object Detail which allows us to see the entire details of the Each and every objects created by the partitions.

 

CREATE VIEW PH.ObjectDetail        

AS

SELECT 

        SCHEMA_NAME(so.schema_id) AS schema_name ,

        OBJECT_NAME(p.object_id) AS object_name ,

        p.partition_number ,

        p.data_compression_desc ,

        dbps.row_count ,

        dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,

        si.index_id ,

        CASE WHEN si.index_id = 0 THEN '(heap!)' ELSE si.name END AS index_name ,

        si.is_unique ,

        si.data_space_id ,

        mappedto.name AS mapped_to_name ,

        mappedto.type_desc AS mapped_to_type_desc ,

        partitionds.name AS partition_filegroup ,

        pf.name AS pf_name ,

        pf.type_desc AS pf_type_desc ,

        pf.fanout AS pf_fanout ,

        pf.boundary_value_on_right ,

        ps.name AS partition_scheme_name ,

        rv.value AS range_value

FROM  sys.partitions p

        JOIN  sys.objects so ON p.object_id = so.object_id AND so.is_ms_shipped = 0

        LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id AND p.partition_id = dbps.partition_id

        JOIN sys.indexes si ON p.object_id = si.object_id AND p.index_id = si.index_id

        LEFT JOIN sys.data_spaces mappedto ON si.data_space_id = mappedto.data_space_id

        LEFT JOIN sys.destination_data_spaces dds ON si.data_space_id = dds.partition_scheme_id

        AND p.partition_number = dds.destination_id

        LEFT JOIN sys.data_spaces partitionds ON dds.data_space_id = partitionds.data_space_id

        LEFT JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id

        LEFT JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id

        LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id AND

        dds.destination_id = CASE pf.boundary_value_on_right WHEN 0 THEN rv.boundary_id ELSE rv.boundary_id + 1 END

GO

 

Note: On Issuing SQL Queries on both the Partition Helper View should bring No data

 

Step 3: Create a table and populate the data with 4 million Records in it

 

Create a table with the name "Partitioned" in order to insert the data and start processing the Partition for these tables

 

; WITH    Pass0 AS (SELECT   1 AS C UNION ALL SELECT   1),

                Pass1 AS (SELECT   1 AS C FROM     Pass0 AS A, Pass0 AS B),

                Pass2 AS (SELECT   1 AS C FROM     Pass1 AS A, Pass1 AS B),

                Pass3 AS (SELECT   1 AS C FROM     Pass2 AS A, Pass2 AS B),

                Pass4 AS (SELECT   1 AS C FROM     Pass3 AS A, Pass3 AS B),

                Pass5 AS (SELECT   1 AS C FROM     Pass4 AS A, Pass4 AS B),

                Partitioned AS (SELECT   row_number () OVER (Order BY C) AS N FROM Pass5)

SELECT N INTO    ph.Partitioned FROM    Partitioned

WHERE   N <= 100000;

GO

 

Using the Select statement check the data were populated in the Database tables as shown in screenshot

Select * from ph.Partitioned

 

Step 4 : Create a Partition Function

 

Create the Partition Function called Daily Partition Function - DailyPF with the "RANGE as RIGHT" Where RIGHT Indicates the Lower boundaries of the Next Partition and LEFT Indicates the Higher boundaries of Previous Partition. it's Highly recommended to use Partition with RIGHT Boundaries.

 

DECLARE @StartDay DATE=DATEADD (dd,-3, CAST (SYSDATETIME () AS DATE));

CREATE PARTITION FUNCTION DailyPF (DATETIME2 (0)) AS RANGE RIGHT FOR

VALUES (@StartDay, DATEADD(dd,1,@StartDay), DATEADD(dd,2,@StartDay), DATEADD(dd,3,@StartDay), DATEADD(dd,4,@StartDay) );

GO

 

Note: Values with the Bracket of VALUES are called as Boundaries as shown in Screenshot

Using the below query, we can check, whether partition function has been created in the Database

 

Select * From Sys.Partition_Functions 

 

SELECT Name, Type_Desc, Fanout, Boundary_value_on_right, Create date FROM sys.partition_functions;

GO 

 

Step 5: Setting Up Some File Groups and Files for Partitions to Reside On
Add File Groups to Database using the SQL Queries below.

Number of Filegroups = 1 + Number of boundary points defined in partition function

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG1

GO

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG2

GO

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG3

GO

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG4

GO

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG5

GO

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG6

GO

 

Once the File Groups are created, add the Files into File Group Dynamically using the SQL Queries given below

 

DECLARE @path NVARCHAR(256), @i TINYINT=1, @sql NVARCHAR(4000);

SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4) FROM sys.database_files

WHERE name='PartitionDaniel';

WHILE @i <= 6

BEGIN

                SET @sql=N'ALTER DATABASE PartitionDaniel ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+', 

                                 filename=''' +  @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',

                                 size=128MB, filegrowth=256MB) TO FILEGROUP DailyFG'+ CAST(@i AS NCHAR(1))

                RAISERROR (@sql,0,0)

                Print (@sql)

                EXEC sp_executesql @sql;

                SET @i+=1;

END

GO

 

the Files will be created as Highlighted in the screenshot below

File for the respective FileGroups  are created as shown in below Screenshot

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



Step 6: Create Partition Schema

Create a Partition Schema in order to map the File Group to Partition Function

CREATE PARTITION SCHEME DailyPS

AS PARTITION DailyPF TO (DailyFG1, DailyFG2, DailyFG3, DailyFG4, DailyFG5, DailyFG6);

 

Execute the below Query to find the Mapping done between the Partition Schema and File Group

SELECT * FROM ph.FileGroupDetail;

Creating Partitioned heap is possible. Create a table Name OrdersDaily using the script below. Using the Schema DailyPS which take the Parameter called OrderDate by which the Data are separated in the File Group as shown

 

if OBJECT_ID('OrdersDaily','U') is null
CREATE TABLE OrdersDaily
(

                OrderDate DATETIME2(0) NOT NULL,

                OrderId int IDENTITY NOT NULL,
                OrderName nVarchar(256) NOT NULL
) on DailyPS(OrderDate)
GO

Let's Insert the records into the OrderDaily table and let's leave the Partition 1 and Partition 6 as Empty for certain purpose to do Sandwiching and Switches

INSERT OrdersDaily(OrderDate, OrderName)
SELECT
            DATEADD(ss, t.N, DATEADD(dd,-3,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
            CASE WHEN t.N % 3 = 0 THEN 'Robot' WHEN t.N % 4 = 0 THEN 'Badger'  ELSE 'Pen' END AS OrderName
FROM ph.Partitioned AS t
WHERE N < = 1000;

--Two days ago = 2000 rows

INSERT OrdersDaily(OrderDate, OrderName)

SELECT
              DATEADD(ss, t.N, DATEADD(dd,-2,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
              CASE WHEN t.N % 3 = 0 THEN 'Flying Monkey' WHEN t.N % 4 = 0 THEN 'Junebug'  ELSE 'Pen' END AS OrderName
FROM ph.Partitioned AS t
WHERE N < = 2000;
 
--Yesterday= 3000 rows
INSERT OrdersDaily(OrderDate, OrderName)
SELECT
              DATEADD(ss, t.N, DATEADD(dd,-1,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
              CASE WHEN t.N % 2 = 0 THEN 'Turtle' WHEN t.N % 5 = 0 THEN 'Eraser'  ELSE 'Pen' END AS OrderName
FROM ph.Partitioned AS t
WHERE N < = 3000;

--Today=  4000 rows

INSERT OrdersDaily(OrderDate, OrderName)
SELECT
             DATEADD(ss, t.N, CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0))) AS OrderDate,
             CASE WHEN t.N % 3 = 0 THEN 'Lasso' WHEN t.N % 2 = 0 THEN 'Cattle Prod'  ELSE 'Pen' END AS OrderName
FROM ph.Partitioned AS t
WHERE N < = 4000;
GO

So there will be almost 10,000 records present in the OrdersDaily table. Now look at the Heap using the Partition Helper Views which we had already created and by using the below queries.

Note: Partition 1 and Partition 6 are Empty as discussed Earlier.

Step 7: Add  the Primary Key to Order Daily Table

Once the primary key is added the Heap will get disappeared
 

ALTER TABLE OrdersDaily ADD CONSTRAINT PKOrdersDaily PRIMARY KEY CLUSTERED(OrderDate, OrderId)
GO

Create the Non Clustered Index - NCI which is called as Aligned with the Index ID = 2 as given below
CREATE NONCLUSTERED INDEX NCOrderIdOrdersDaily ON OrdersDaily(OrderId)

Once Created, the table will have both Clustered and Non Clustered Index on the Table

Create the Non Clustered Index - NCI which is called as Non Aligned with the Index ID = 3. its based on the Primary File Group as given below

CREATE NONCLUSTERED INDEX NCOrderNameOrdersDailyNonAligned ON OrdersDaily(OrderName) ON [PRIMARY]

Execute the below query to see the Sandwich created by the Indexes
 

SELECT partition_number, row_count, range_value, reserved_mb, index_id, index_name,mapped_to_name,mapped_to_type_desc, partition_filegroup, pf_name FROM ph.ObjectDetail  WHERE object_name='OrdersDaily' order by index_name, partition_number

Step 8 : Creating New File Group, File and New Partitions

ALTER DATABASE PartitionDaniel ADD FILEGROUP DailyFG7

 

DECLARE @path NVARCHAR(256), @i TINYINT=7, @sql NVARCHAR(4000);

SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4)

FROM sys.database_files WHERE name='PartitionDaniel';

WHILE @i = 7

BEGIN

                SET @sql=N'ALTER DATABASE PartitionDaniel ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+',

                                 filename=''' +  @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',

                                 size=128MB, filegrowth=256MB) TO FILEGROUP DailyFG' + CAST(@i AS NCHAR(1))

                RAISERROR (@sql,0,0)

                EXEC sp_executesql @sql

                SET @i+=1

End

Create a stage table which load the Daily data into a table called  Orders Daily Load. The File Group should be same as the Primary table OrdersDaily. I.e. We are going to load the data into the Last File Group which was empty. In the above examples we have File Group "DailyFG6" which is the Last File Group of the OrdersDaily table. So If you look at the code below it has "On [DailyFG6]"

CREATE TABLE OrdersDailyLoad

(

                OrderDate DATETIME2(0) NOT NULL,

                OrderId int IDENTITY (10001,1) NOT NULL,

                OrderName nVarchar(256) NOT NULL

) on [DailyFG6]

GO

INSERT OrdersDailyLoad(OrderDate, OrderName)

SELECT

                DATEADD(SECOND, t.N, DATEADD(dd,1,CAST(SYSDATETIME() AS DATETIME2(0)))) AS OrderDate,

                CASE WHEN t.N % 3 = 0 THEN 'Bow and Arrow' WHEN t.N % 2 = 0 THEN 'First Aid Kit' ELSE 'Pen' END OrderName

FROM ph.Partitioned AS t WHERE N < = 5000

GO

ALTER TABLE OrdersDailyLoad ADD CONSTRAINT PKOrdersDailyLoad PRIMARY KEY CLUSTERED(OrderDate,OrderId)

GO

CREATE NONCLUSTERED INDEX NCOrderIdOrdersDailyLoad ON OrdersDailyLoad(OrderId)

GO

In the below query we are using the Check Constraint which allow only those records which qualifies or satisfy the Condition will move into the File Group and Partitions
 

DECLARE @tsql NVARCHAR(2000)=
'ALTER TABLE OrdersDailyLoad WITH CHECK ADD CONSTRAINT CKOrdersDailyLoad_LowEndCHECK (OrderDate >= ''' +  convert(CHAR(10),DATEADD(dd,1,CAST(SYSDATETIME() AS DATE))) + ''')'
RAISERROR (@tsql,0,0)
EXEC sp_executesql @tsql;
GO

 
DECLARE @tsql NVARCHAR(2000)=
'ALTER TABLE OrdersDailyLoad WITH CHECK ADD CONSTRAINT CKOrdersDailyLoad_HighEnd
CHECK (OrderDate < ''' +  convert(CHAR(10),DATEADD(dd,2,CAST(SYSDATETIME() AS DATE))) + ''')'
RAISERROR (@tsql,0,0)
EXEC sp_executesql @tsql;

On applying the below SQL Query , Partition File Group should move to next level
ALTER PARTITION SCHEME DailyPS NEXT USED DailyFG7

ALTER PARTITION FUNCTION DailyPF() SPLIT RANGE (DATEADD(dd,2,CAST(SYSDATETIME() AS DATE)))

Before Switching the data from Orders Daily Load to Orders Daily table. Disable the Non Aligned Non Clustered Index which was created earlier. There are 3 Indexes created in the Partitions where Indexes with Index Id = 1 are called as Partitioned Clustered Index which has N-Number of Partitions. Indexes with Aligned Non Clustered Indexes are called as Partitioned Non Clustered Index which has  N-Number of Partitions where as the Non Aligned Non Clustered Indexes are Created on the Primary Group which will have only one Index on the table where all the rows will be included with in that Index.

Alter Index NCOrderNameOrdersDailyNonAligned On OrdersDaily Disable;

Step 9 : Switch IN Concept

Switch the Data from OrdersDaily Load table to OrdersDaily table using the SWITCH Command as shown below

ALTER TABLE OrdersDailyLoad SWITCH TO OrdersDaily PARTITION 6

 

Query the Select statement below and once the Switching is done, Drop the Load table

SELECT * FROM ph.ObjectDetail WHERE object_name IN ('OrdersDaily','OrdersDailyLoad')

ORDER BY object_name, partition_number;

Go

DROP TABLE OrdersDailyLoad;

Go

Step 10 : Switch OUT Concept

Similarly create a Database table called "OrdersDailyOut" and Specify which File Group needs to be taken out of the Main table. In the Example the DailyFG2 is the File Group needs to be taken out of Main table

CREATE TABLE OrdersDailyOut

(

                OrderDate DATETIME2(0) NOT NULL,

                OrderId int IDENTITY NOT NULL,

                OrderName nVarchar(256) NOT NULL

) on [DailyFG2];

GO 

ALTER TABLE OrdersDailyOut ADD CONSTRAINT PKOrdersDailyOut  PRIMARY KEY CLUSTERED(OrderDate,OrderId);

RAISERROR ('Switching out.',0,0)

ALTER TABLE OrdersDaily SWITCH PARTITION 2 TO OrdersDailyOut;

 

Once the Switching  Out is done, then Merging the Unwanted partition needs to be done

 

Step 11 : Merge Unwanted Partitions

Use the SQL Query below to Merge the Unwanted Partitions

DECLARE @MergeBoundaryPoint DATETIME2(0), @msg NVARCHAR(2000);

SELECT @MergeBoundaryPoint = CAST(MIN(rv.value) AS DATETIME2(0))

FROM sys.partition_functions  pf

JOIN sys.partition_range_values rv ON pf.function_id=rv.function_id

where pf.name='DailyPF'

IF (SELECT COUNT(*) FROM dbo.OrdersDaily WHERE OrderDate < dateadd(DAY, 1, @MergeBoundaryPoint)) =0

BEGIN

             SET @msg='No records found, merging boundary point '  + CAST(@MergeBoundaryPoint AS CHAR(10)) + '.'

             RAISERROR (@msg,0,0)

             ALTER PARTITION FUNCTION DailyPF ()  MERGE RANGE ( @MergeBoundaryPoint )

END

ELSE

BEGIN

     SET @msg='Record exist around boundary point ' + CAST(@MergeBoundaryPoint AS CHAR(10)) + '. Not Merge'

     RAISERROR (@msg,16,1)

END

Once the Merge is Done the Data looks like the below where "Partition 2 Contained 1000 Record was Missing"

Regards

Daniel J

Dataware and Datamining Architect...