Attaining Reusability in SQL Server: Part II

Safety concept: Access on computer keyboard background

17 Nov

This is a follow-up to a post made a few weeks ago that you can find here.

 

Maybe you believe me that some of the patterns I outlined last time are worth looking at, but you’re still not sold since you haven’t seen the implementation yet. I don’t blame you. I have to see a few examples of something new before I buy into it.

Today I’m going to take you through a very simple example of using UDTTs and TVFs across a simple stored procedures to do some basic statistics gathering. I won’t go into specifics about performance as I think the supporting articles I listed last time have enough evidence to convince you that TVFs are performant. Instead, I will be focusing on the details needed to fully start using TVFs and UDTTs in a reusable way. Let’s get started.

The Scenario

Let’s say we have a really basic order database. We have a base table called orders (I know, I know. My naming convention is a little crazy..), that has local_orders,  domestic_orders and  international_orders indicated by an order_type  (1, 2, and 3 respectively). These orders move along the order process via an order_status column that for simplicity has order_placed, order_fulfilled, and order_shipped (again 1, 2, and 3 respectively). We also have a timestamp for when the order is placed, fulfilled and shipped.

Lets also say we want to provide a dashboard with metrics per order type that are quickly accessible. We don’t want to have to run large queries whenever an administrator goes to see these metrics. In order to provide this in a such a way that we don’t have to query the orders table every time, we can gather smaller snapshots of statistics and store them in another table order_stats.

The Implementation

This type of scenario works really well for the objects and patterns I have talked about. To implement, we will need the following:

  • A TVF for gathering statistics from the orders table
  • A stored procedure for taking the values returned from the function above and store them in the order_stats table
  • A TVF for gathering statistics from a set of records from the order_stats table
  • A UDTT that we can pass to the function above that mimics the order_stats table.

The key component again to make some of this work is the TVF function that accepts a temporary table of the UDTT we will create. In my example these are the tvf_gather_analytics function and the udtt_order_stats UDTT. The  tvf_gather_analytics takes in an argument named @stats which is of the udtt_order_stats type. The actual code the the function is below:

 

Full Example

The full example can be downloaded here.

Screen Shot 2015-11-18 at 10.45.37 AM

The create_database_ExampleDB.sql should be the only script you need to set up the database and create all of the necessary objects. All of the specific objects are contained as well as separate scripts if you want to review them more closely. There is also an examples.sql script that should take you through inserting some data and using each of the objects discussed.

Hope this small example has been helpful. There are ton of other scenarios and use-cases that this set of tools can help you address (we use it internally for rating calls!). I’m sure if you look hard-enough at your database, you can find a place to put this into practice.

Dan Goslen
Dan Goslen
dgoslen@bandwidth.com

Dan Goslen is a Software Developer at Bandwidth working on our Billing Platform. He went to NC State after being a raised a Wolfpack fan and graduated with a degree in Computer Science in 2013. He was first exposed to programming when his parents bought him a LEGO Mindstorms set when he was a kid and has been hooked ever since. Being passionate about new ideas and how they evolve, Dan is also helping with our Sprint Sabbatical program. When not coding, Dan can be found playing Ultimate Frisbee, or enjoying a cup of French Pressed coffee.

No Comments

Post A Comment