Partitioning in SQL Server - Part 2

Please read my previous post Partitioning in SQL Server - Part 1 before reading this post.

In my earlier post, I've discussed about theoretical concept of Partitioning. In this post I will speak about how partitioning is done. Remember the following procedure I'm going to show is available in SQL Server Enterprise.

Let's try different types of tables. To make use of partition, it would be good if the table has huge amount of data. So let's do the same.

USE AdventureWorksDW2012

GO

CREATE TABLE MySampleTable (ID INT PRIMARY KEY, CREATE_DATE DATETIME)

INSERT MySampleTable

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY O.NAME),O1.create_date

FROM sys.all_objects O

CROSS JOIN sys.all_objects O1

Now 1000000 records are inserted in our table. Partition can be made in different cases. Now I'll show how it is done using SQL Server Management Studio (SSMS).

Step - 1

Before partitioning a table, make sure that files exist for partitioning. There is no need to have the partitions distributed among different file groups but it is recommended because individual maintenance can be taken so that the other file will not get disturbed due to maintenance. So the first step that should be taken is creating a file in a different file group. Let's do that.

Go to Object Explorer and right click on the database in which the table that has to be partitioned exists. Go to Properties and then Files. There you can see the data file and log file of the database. Now click on Add button which adds a row below them.

Give a name for the file under the section Logical Name. Scroll to the right where you find a section Path. There give a path which is different from the drive in which SQL Server is present. You can give a name for the file and scroll back to the left. Under Filegroup section, select <new-filegroup> option in the drop-down which leads you to a new window as shown below.

Specify a name for the new file group. Below are two options. If you select Read-Only then the data in that file group cannot be updated. It can be just read. If you select Default then any data inserted into the table resides in this file group. I will write about these file groups in a different post. For now remember these points. Click on OK after giving the name and then click OK on the previous dialog box which creates a new file group on your disk.

Step - 2

Go to Object Explorer and find the table created. Right Click on it and there exists an option STORAGE. Expand it and click on Create Partition.

Step - 3

A Partition Wizard appears, click on NEXT which then guides you to Select a Partition Column. There select a column. To demonstrate a different kind of partition, choose the one which has datetime datatype.

There are two options with check boxes below. If you check the first option, you can use any existing partition scheme for this table. If you check the second option, all the indexes present on the table are partitioned with the same scheme the present table is being partitioned. For now, I'm skipping both the options.

Click on Next button to go to next step.

Step - 4

The next step is Select a Partition Function,

If you want to create a New Partition Function, type a name for it or else if there is any table partitioned already and you feel that it would be good to use that, click on Existing Partition Function and then select one.

Click on Next after selecting a Partition Function.

Step - 5

The next step is Select a Partition Scheme. The same case as above is with this. Follow the same criteria specified above.

Click on Next button for next step.

Step - 6

Here comes the crucial phase which decides the future of your table, Map Partitions. As we are partitioning on datetime column, the option Set Boundaries is highlighted. Choose a range, Left Boundary or Right Boundary and then click on Set Boundaries button. It opens a new dialog box as below,

As it is a datetime column, SQL Server sets default boundaries with the minimum value (Start Date) and maximum (End Date) value of that column. Based on that, you can select Date Range among the provided five options. Now click on OK in the Set Boundary Values dialog box and go the previous dialog box.

You can find that SQL Server specified the partitions with Boundary Values, Rowcount and Required Space. Now specify the file groups from the drop-down list. You can also find the new file group created, in that list. Select file groups for all the partitions and click on Estimate Storage which gives you the total storage capacity required for the partitions.

In my earlier post on Partition, I specified a case where one file group is specified more. You can see that practically now in the above screenshot. 

Click on Next button now.

Step - 7

Now the final step is arrived where you have some options as shown in the below screenshot.

• You can create T-SQL script for the above procedure.
• You can Run Immediately the above procedure which creates partitions immediately.
• You can schedule the partitioning later by specifying a desired date and time.
• You can save the script to a SQL file or you can copy the script to Clipboard or a New Query Window.

I selected the default options to explain how partitioning is done. Click on Finish button twice which creates the following T-SQL script for me in a new query window.

USE [AdventureWorksDW2012]

GO

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [MySampleTable_PF](datetime) AS RANGE LEFT FOR VALUES (N'2003-04-08T00:00:00', 

        N'2004-04-08T00:00:00', 

        N'2005-04-08T00:00:00', 

        N'2006-04-08T00:00:00', 

        N'2007-04-08T00:00:00', 

        N'2008-04-08T00:00:00', 

        N'2014-04-08T00:00:00')

CREATE PARTITION SCHEME [MySampleTable_PS] 

AS PARTITION [MySampleTable_PF] 

TO 

([PRIMARY], [PRIMARY], [PRIMARY], [New_Filegroup], [New_Filegroup], [New_Filegroup], [New_Filegroup])

ALTER TABLE [dbo].[MySampleTable] DROP CONSTRAINT [PK__MySample__3214EC27650F907D]

ALTER TABLE [dbo].[MySampleTable] ADD PRIMARY KEY NONCLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_MySampleTable_PS_635291384639364538] ON [dbo].[MySampleTable]

(

[CREATE_DATE]

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [MySampleTable_PS]([CREATE_DATE])

DROP INDEX [ClusteredIndex_on_MySampleTable_PS_635291384639364538] ON [dbo].[MySampleTable]

COMMIT TRANSACTION

In the above T-SQL script, half of the script was executed as we discussed before. Now the remaining half script is important to discuss.

• The existing Primary Key on the column ID is dropped because Primary Key creates a Unique Clustered Index on the table which is responsible for the physical storage of data on the disk. Here the table is being divided into parts which affects the physical storage, so it is dropped.
• Later the Primary Key is created as Non-Clustered.
• Then a Clustered Index is created on the partitioning datetime column. The reason for this is, one of the Microsoft recommended strategies that Partitioning column should be a Clustered Indexed Column as it affects the physical storage of the data. As only one clustered index can exist per table, SQL Server created the Primary Key as Non-Clustered in the above step.
• Finally the Clustered Index created on datetime column is dropped because it is not the criteria of original table's schema. If we had created clustered index on this column while creating the table the index wouldn't have dropped or had we made partition on the column ID, Primary Key is dropped and later created as Primary Key Clustered.

Run the above T-SQL script, Partitioning is done and it can be checked with the below query,

SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('MySampleTable')

This is how Partitioning is done using SSMS. As partitioning on a datetime column is a peculiar case, I've written about that. Please try different cases of partitioning so that you will get a better grip on the concept.

Read the official documentation of Microsoft on Partitioning in the below link,

http://technet.microsoft.com/en-us/library/ms188730.aspx