sql server table partition function

by Marlon Ribunal

How To Split Table Partitions Automatically

Table partitions can bring a lot of benefits to the manageability and performance of your SQL Server – that is if the partitions are configured and designed correctly. If you’ve got a large table with hundreds of millions of records, and you need to maintain a portion of that table only and not necessarily the whole table, table partitioning allows you to perform that maintenance on a partition-by-partition basis. And, also, when you enable lock escalation on the partition level, many of your locking and blocking woes might be fixed. If you have issues of locking/blocking on a big table in an application that performs high concurrency, table partitioning might help. Brent Ozar Unlimited has some good resources on SQL Server Table Partitioning.

What I want to focus on this post is the splitting of partition. It is very important to note that splitting a populated partition would cause the log file to bloat and, in some cases, the TempDB to run out of space. But you might have to deal with this if you’re adding partitioning in an existing table.

If you are still in the designing phase of your application development and you might be thinking of implementing table partitioning in the backend, then adding a task that would add partitions dynamically as your table grows should be on top of your to-do list. Creating the partitions in advance and staying few steps ahead of your data can save you from a lot of stress and headaches.

Here’s an example of how easy it is to implement a task that will automatically create partitions.

Test Table Partitions

Let’s create a test table. For this demo, we’re going to use a single filegroup (Primary) for convenience purposes.

At this point, without any records inserted yet to the table, the sys.partitions Object Catalog View would show something like this:
sys.partitions None Partitioned Table You can pull this metadata with this query:

Partition Function and Scheme

Let’s create the Partition Function and Scheme. Again, for convenience purposes, let’s create the Scheme on a single filegroup, which is the Primary filegroup in this case. Let’s supposed we want to partition our data into month buckets. Since this a date, I’m using Left Range which means the partition uses the upper boundary and includes all the points to its left. If you use Right Range instead, then you have to have a logic to handle the 31st day of some of the months. Just to illustrate that:

LowerBoundaryValue UpperBoundaryValue
NULL 1/1/2017
1/1/2017 2/1/2017

Upon successful creation of the Partition Function and Scheme, you’ll get a message something like this:

Add Indexes

Let’s also create indexes for demo purposes.

Populate Test Table

Although I said that you may want to avoid splitting populated partitions, for the purpose of this blog post, let’s create a single partition, which we already did above, then populate the table with data and add another partition on the populated table.

I am using Redgate SQL Data Generator to populate the test table with these settings on our Partition Column (SalesDate):

Redgate SQL Data Generator


You may remember that we created a partition (1/1/2017). Now with the 1,000 records added, our Table Partition looks like this:

populated table partition

Create Additional Partition

So, let’s create 4 additional partitions:

Insert Additional Records

Let’s insert few new records under April.

So far, we have 5 partitions for each month (January, February, March, April, and May). So we can have a better picture of our table partitions with the additional partitions and data, let’s query the underlying objects:

Which will give us this (click on the picture to enlarge it):

Table Partitions sql server

Dynamic Table Partition Split

So, let’s pretend it’s the month of April 2017 and this is the partition currently populated. Based on the query above, aside from the current partition bucket, we also have another available bucket month for May.

Say we want to maintain 3 available buckets at any given time. The next available bucket is May, so that means we need 2 more partitions to cover for June and July.

To do that, let’s do something like this:

Now we can see the additional partitions created:

Table Partitions sql server additional table partitions

Run the same code without changing the number of buckets to maintain (3) and you will get this message:

To make this truly automated, create a job that will run this code on monthly basis.


SQL Operations Studio Chart

by Marlon Ribunal

SQL Operations Studio As Monitoring Dashboard

Microsoft as a company has entered a new phase of innovation under the leadership of their CEO Satya Nadella. In the database world, few of the things that came out of this innovation are SQL Server for Linux, Azure SQL Database, Azure SQL Datawarehouse, etc. As the technology stack, in general, is undergoing a radical shift, the tools that interface to these technologies must, therefore, evolve along.

SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT) are the de facto tools used by both SQL Server Database Administrators and SQL Developers for Database Administration and Development. As more and more functions are required of these tools as a direct result of the ever-changing technology landscape, these tools may fall short in some aspects. This is most true for other people that need connectivity to their databases to manage and monitor them, especially the Application Developers and DevOps Engineers.

Database Administrators, developers, DevOps, and those that are involved in the business operations need a flexible tool that they can utilize to deliver solutions effectively to the business. And, equally important is that this tool must be technology agnostic.

This is where SQL Operations Studio (SOS) comes in the picture. SOS is a lightweight, cross-platform client. It is also open-source. Aside from its IDE functionalities, SOS has a lot of more offerings for DBAs, Devs, and DevOps. One of these is that you can use it as a dashboard to monitor your databases. SSMS comes with standard reports as well as custom reports using Report Definition Language or RDL (which is essentially an SSRS-like report residing in SSMS). SQL Operations Studio raised the bar in this regard. You can create multiple widgets to display on your dashboard.

SQL Operations Studio Chart

I have here a query that returns disk latency info. Here’s how easy to add this info as a widget in the SQL Operations Studio Dashboard:

First, view the results as a Chart (see screenshot above). Click Create Insight to pull the JSON code behind the chart. Copy this code.

SQL Operations Studio Chart JSON


Go to the User Setting tab (Click CTRL+ Comma if you’ve closed it). In the search box, type dashboard.database.widgets. If you hover your cursor over the code, that will show a pencil icon. Click the pencil icon to Edit/Copy the Setting to the dashboard. Paste the whole block of JSON code from the Insight tab. Then save user setting.

If you go to the Server panel, right-click on the database and select Manage, you should now see something like this (I added another widget in this case):

SQL Operations Studio Dashboard


Tech Reading List of 2018

by Marlon Ribunal

My Tech Reading List to Start the Year

My bold learning goals call for adequate preparation and proportionate action. Before plunging into the challenge, I need to get myself into a focused learning mode quickly. The most effective way for me to do that is by reading. If you are like me, every time that you want to learn something, the first thing that you would do is look for written materials on whatever topic that you want to learn.

Tech Reading List of 2018My medium preference is still the physical book. There are many benefits to reading printed materials such as brain stimulation and memory improvement. Reading online materials like blogs and articles is a big part of my daily routine. But when I need to sit down and focus on certain things that I want to learn deeply, I’d pull a physical book from my bookshelf and just put in the needed concentration and time to dissect and slice and dice every bit of information that I need to process into every available neural pathway of my brain.

Here is the first batch of technical books that I am reading to start the new year:


Star Schema: The Complete Reference by Christopher Adamson – You’d say, “Hey, Marlon, in the Era of Big Data and Hadoop, Dimensional Modeling is Dead! The traditional data warehouse has long been replaced by data lakes!” Ok, let me leave you that to debate among yourselves. What this Star Schema book teaches are the design principles that will not fall into obsolescence and will transcend beyond technology. What our business processes demand of us, data professionals, is that we not only deliver the data structure that will support the evaluation of these business processes but also the measures and their context with which the data are evaluated.


The Data Warehouse ETL Toolkit by Ralph Kimball & Joe Caserta – “Again, Marlon, I have a couple of words for you, “Hadoop it! ETL is likewise Dead!” Like the Star Schema book, this ETL Toolkit teaches the fundamental principles and techniques of extraction, transformation, and loading of data. Technologies surrounding how we extract, transform, and load data may have changed but the principles remain the same. This book will take you back to the basics, and regardless of technology platform, the principles you’ll learn here will equip you with the necessary skills to adapt to the ever-changing technology landscape.


Agile Data Warehouse Design: Collaborative Dimensional Modeling, From Whiteboard to Star Schema by Lawrence Corr – “Marlon, stop it! Data Warehouse is Dead!” Again, because of the principles that this book advocate, I have it on my list. If you’re looking for agile principles that you need to be guided by in your data projects, this is a great book to have. Dimensional modeling as a philosophy is alive and kicking. This book will make you learn the right questions to ask about your data stories: the who, what, when, where, how many, and why and how. This is also a good introduction to the concept of Business Event Analysis & Modeling (BEAM) if you’ve never heard about the concept before.


Storytelling with Data: A Data Visualization Guide for Business Professionals by Cole Nussbaumer Knaflic – Your data projects will be meaningless if they are not capable of telling their stories. With the era of Big Data revolution, we are inundated with more data and information than we can possibly handle at a time. This book teaches us that “Data Visualization sits at the intersection of science and art.” More important than the data themselves are the meanings and stories they convey. As data professionals, we just don’t deliver data solutions. We also need to deliver the effective stories behind those data. This book teaches us the right principles on how to effectively do just that.


Pro SQL Server Internals: Understand What Happens Under the Hood and How It Affects You by Dmitri Korotkevitch and High-Performance SQL Server: Bringing Consistent Response Time to Mission-Critical Applications by Benjamin Nevarez – Big part of my day job is to ensure that our client applications are running on optimal performance. These two books are great resources for making your SQL Server run faster. They provide sound principles for database maintenance and performance tuning.


I highly recommend these books. If you’re looking for books to grab on these topics, pick one or all of them. I’d like to hear about your tech reading list. Share them in the comment below or, if you have a post about your tech reading list, provide the link to the comment below. Happy New Year and Happy Learning!